none
Converting 3 Rows to 1 column RRS feed

  • Question

  • I'm looking for an efficient way to convert rows to columns in SQL server. any help on query appreciated.

    This is my example:

    CREATE TABLE #test (
    Name VARCHAR(100),
    Cou VARCHAR(50),
    Dom VARCHAR(50),
    Deli VARCHAR(50),
    ClassNum int,
    Inst int,
    Cust int,
    )

    INSERT INTO #test
    VALUES ('Keely','TEST','None - No Domain','Virtual',2,4.5,2),
    ('Kriste','TEST','None - No Domain','Virtual',2,5.5,6),
    ('Rob','TEST','None - No Domain','Virtual',2,4,6)

    select * from #test

    This is my Result

    Name Count Cou Dom Deli ClassNum Inst Cust
    Keely , Kriste , Rob 3 TEST None - No Domain Virtual 6 14 14

    Wednesday, December 4, 2019 6:44 PM

Answers

  • CREATE TABLE #test (
    Name VARCHAR(100),
    Cou VARCHAR(50),
    Dom VARCHAR(50),
    Deli VARCHAR(50),
    ClassNum int,
    Inst int,
    Cust int,
    )
    
    INSERT INTO #test (Name,
    Cou ,
    Dom ,
    Deli ,
    ClassNum ,
    Inst ,
    Cust)
    VALUES ('Keely','TEST','None - No Domain','Virtual',2,4.5,2),
    ('Kriste','TEST','None - No Domain','Virtual',2,5.5,6),
    ('Rob','TEST','None - No Domain','Virtual',2,4,6);
     
    SELECT
           Stuff(( SELECT ',' + Name
               FROM #test t2
              WHERE t2.Cou= t1.Cou and  t2.Dom = t1.Dom
              ORDER BY Name
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
    			
    ,Count(*) as [Count]
    ,t1.Cou
    ,t1.Dom
    ,t1.Deli
    ,SUM(t1.ClassNum) ClassNum
    ,SUM(t1.Inst) Inst
    ,SUM(t1.Cust) Cust
      FROM #test t1
     GROUP BY t1.Cou,t1.Dom,t1.Deli;
     
     /* SQL Server 2017,2019 */
    
     /*
     Select string_agg(Name,',') Name
     ,Count(*) as [Count]
    ,t1.Cou
    ,t1.Dom
    ,t1.Deli
     ,SUM(t1.ClassNum) ClassNum
    ,SUM(t1.Inst) Inst
    ,SUM(t1.Cust) Cust
     FROM #test t1
      GROUP BY t1.Cou,t1.Dom,t1.Deli;
      */
    
    
      /*
      Names	Count	Cou	Dom	Deli	ClassNum	Inst	Cust
    Keely,Kriste,Rob	3	TEST	None - No Domain	Virtual	6	13	14
    
      */
    drop TABLE #test 
    

    • Marked as answer by Nav144 Wednesday, December 4, 2019 7:11 PM
    Wednesday, December 4, 2019 7:03 PM
    Moderator
  • Hi Nav144

    2 Examples:

    CREATE TABLE #test (
    Name VARCHAR(100),
    Cou VARCHAR(50),
    Dom VARCHAR(50),
    Deli VARCHAR(50),
    ClassNum int,
    Inst int,
    Cust int,
    )
    
    INSERT INTO #test
    VALUES ('Keely','TEST','None - No Domain','Virtual',2,4.5,2),
    ('Kriste','TEST','None - No Domain','Virtual',2,5.5,6),
    ('Rob','TEST','None - No Domain','Virtual',2,4,6)
    
    select * from #test
    
    DECLARE @Xmldata XML = (SELECT * FROM #test FOR XML PATH('') ) 
    
    --Example 1
    SELECT * INTO #temp1 FROM (
    SELECT * FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY i.value('local-name(.)','varchar(100)') ORDER BY x.i) AS rn,
    	   i.value('local-name(.)','varchar(100)') ColumnName,
           i.value('.','varchar(100)') ColumnValue
    FROM @xmldata.nodes('//*[text()]') x(i) ) tmp ) tmp1
    
    SELECT * FROM #temp1
    
    SELECT * INTO #temp2 FROM (
    SELECT 
    ROW_NUMBER()OVER(PARTITION BY ColumnName ORDER BY ColumnValue) rn,* FROM (
    SELECT i.value('local-name(.)','varchar(100)') ColumnName,
           i.value('.','varchar(100)') ColumnValue
    FROM @xmldata.nodes('//*[text()]') x(i) ) tmp ) tmp1
    
    
    --Example 2
    DECLARE @Columns NVARCHAR(MAX),@query NVARCHAR(MAX)
    SELECT @Columns = STUFF(
     (SELECT  ', ' +QUOTENAME(CONVERT(VARCHAR,rn)) FROM
     (SELECT DISTINCT rn FROM #temp2 ) AS T FOR XML PATH('')),1,2,'') 
    SET @query = N'
    SELECT ColumnName,' + @Columns + '
    FROM
    (
      SELECT * FROM #temp2
    )  i
    PIVOT
    (
      MAX(ColumnValue) FOR rn IN ('
      + @Columns
      + ')
    )  j ;';
    
    EXEC (@query)
    --PRINT @query
    
    
    DROP TABLE #test
    DROP TABLE #temp1
    DROP TABLE #temp2

    Regards

    • Marked as answer by Nav144 Wednesday, December 4, 2019 7:24 PM
    Wednesday, December 4, 2019 7:15 PM

All replies

  • CREATE TABLE #test (
    Name VARCHAR(100),
    Cou VARCHAR(50),
    Dom VARCHAR(50),
    Deli VARCHAR(50),
    ClassNum int,
    Inst int,
    Cust int,
    )
    
    INSERT INTO #test (Name,
    Cou ,
    Dom ,
    Deli ,
    ClassNum ,
    Inst ,
    Cust)
    VALUES ('Keely','TEST','None - No Domain','Virtual',2,4.5,2),
    ('Kriste','TEST','None - No Domain','Virtual',2,5.5,6),
    ('Rob','TEST','None - No Domain','Virtual',2,4,6);
     
    SELECT
           Stuff(( SELECT ',' + Name
               FROM #test t2
              WHERE t2.Cou= t1.Cou and  t2.Dom = t1.Dom
              ORDER BY Name
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
    			
    ,Count(*) as [Count]
    ,t1.Cou
    ,t1.Dom
    ,t1.Deli
    ,SUM(t1.ClassNum) ClassNum
    ,SUM(t1.Inst) Inst
    ,SUM(t1.Cust) Cust
      FROM #test t1
     GROUP BY t1.Cou,t1.Dom,t1.Deli;
     
     /* SQL Server 2017,2019 */
    
     /*
     Select string_agg(Name,',') Name
     ,Count(*) as [Count]
    ,t1.Cou
    ,t1.Dom
    ,t1.Deli
     ,SUM(t1.ClassNum) ClassNum
    ,SUM(t1.Inst) Inst
    ,SUM(t1.Cust) Cust
     FROM #test t1
      GROUP BY t1.Cou,t1.Dom,t1.Deli;
      */
    
    
      /*
      Names	Count	Cou	Dom	Deli	ClassNum	Inst	Cust
    Keely,Kriste,Rob	3	TEST	None - No Domain	Virtual	6	13	14
    
      */
    drop TABLE #test 
    

    • Marked as answer by Nav144 Wednesday, December 4, 2019 7:11 PM
    Wednesday, December 4, 2019 7:03 PM
    Moderator
  • Thank you so much.
    Wednesday, December 4, 2019 7:12 PM
  • Hi Nav144

    2 Examples:

    CREATE TABLE #test (
    Name VARCHAR(100),
    Cou VARCHAR(50),
    Dom VARCHAR(50),
    Deli VARCHAR(50),
    ClassNum int,
    Inst int,
    Cust int,
    )
    
    INSERT INTO #test
    VALUES ('Keely','TEST','None - No Domain','Virtual',2,4.5,2),
    ('Kriste','TEST','None - No Domain','Virtual',2,5.5,6),
    ('Rob','TEST','None - No Domain','Virtual',2,4,6)
    
    select * from #test
    
    DECLARE @Xmldata XML = (SELECT * FROM #test FOR XML PATH('') ) 
    
    --Example 1
    SELECT * INTO #temp1 FROM (
    SELECT * FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY i.value('local-name(.)','varchar(100)') ORDER BY x.i) AS rn,
    	   i.value('local-name(.)','varchar(100)') ColumnName,
           i.value('.','varchar(100)') ColumnValue
    FROM @xmldata.nodes('//*[text()]') x(i) ) tmp ) tmp1
    
    SELECT * FROM #temp1
    
    SELECT * INTO #temp2 FROM (
    SELECT 
    ROW_NUMBER()OVER(PARTITION BY ColumnName ORDER BY ColumnValue) rn,* FROM (
    SELECT i.value('local-name(.)','varchar(100)') ColumnName,
           i.value('.','varchar(100)') ColumnValue
    FROM @xmldata.nodes('//*[text()]') x(i) ) tmp ) tmp1
    
    
    --Example 2
    DECLARE @Columns NVARCHAR(MAX),@query NVARCHAR(MAX)
    SELECT @Columns = STUFF(
     (SELECT  ', ' +QUOTENAME(CONVERT(VARCHAR,rn)) FROM
     (SELECT DISTINCT rn FROM #temp2 ) AS T FOR XML PATH('')),1,2,'') 
    SET @query = N'
    SELECT ColumnName,' + @Columns + '
    FROM
    (
      SELECT * FROM #temp2
    )  i
    PIVOT
    (
      MAX(ColumnValue) FOR rn IN ('
      + @Columns
      + ')
    )  j ;';
    
    EXEC (@query)
    --PRINT @query
    
    
    DROP TABLE #test
    DROP TABLE #temp1
    DROP TABLE #temp2

    Regards

    • Marked as answer by Nav144 Wednesday, December 4, 2019 7:24 PM
    Wednesday, December 4, 2019 7:15 PM