none
Pivot table example RRS feed

  • Question

  • For example I have a table

    Col1      Col2

    1          A

    1          B

    2          A

    2          B

    2          C

    how to turn it to

    col1     col2

    1         "A","B"

    2        "A","B","C"

    ?

    Thursday, September 12, 2019 10:08 AM

All replies

  • Hi

    Please find the below script which will fulfill your need.

    CREATE TABLE #test(Col1 int,Col2 Varchar(10))
    INSERT INTO #test values (1,'A'),(1,'B'),(2,'A'),(2,'B'),(2,'C')
    
    SELECT 
    	Col1 
    	,SUBSTRING((SELECT ',"'+a.Col2+'"' FROM #test a WHERE a.Col1 = b.Col1 FOR XML PATH('')),2,200000)
    FROM #test b
    GROUP BY Col1

    If you are using Azure SQL Datawarehouse - below code is for you

    WITH CTE AS
    (
    SELECT 
    	Col1
    	,CAST(CONCAT('"',Col2,'"') AS VARCHAR(500)) Col2
    	,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) Rno
    	,COUNT(Col1) OVER(PARTITION BY Col1) Cnt
    FROM #test)
    
    ,Main AS
    (
    SELECT Col1,Col2,Rno,Cnt FROM CTE WHERE Rno =1
    UNION ALL
    SELECT a.Col1,CAST(CONCAT(a.Col2,',',b.Col2) AS VARCHAR(500)) AS Col2,b.Rno,b.Cnt FROM Main a
    INNER JOIN CTE b
    ON a.Col1 = b.Col1
    WHERE b.Rno =a.Rno+1)
    
    SELECT 
    	Col1
    	,Col2
    FROM Main
    WHERE Rno=Cnt

    Hope this is helpful!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Thursday, September 12, 2019 10:42 AM
  • Hi

    Just checking in to see if my initial answer helped or you still facing any issues.

     

    If my initial reply answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further query do let us know.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, September 18, 2019 3:09 AM
  • HI Naveen,

    I am using Azure data warehouse and I am getting the below error while executing the above query:

    Recursive common table expressions are not supported in this version.

    Tuesday, October 1, 2019 4:36 AM
  • Hi 

    You are correct, in this case we should go with conventional looping mechanism.

    Below code helps to do the same .

    CREATE TABLE #test(Col1 int,Col2 Varchar(10))
    INSERT INTO #test values (1,'A'),(1,'B'),(2,'A'),(2,'B'),(2,'C')
    
    SELECT 
    	Col1
    	,CAST(CONCAT('"',Col2,'"') AS VARCHAR(500)) Col2
    	,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) Rno
    	,COUNT(Col1) OVER(PARTITION BY Col1) Cnt
    INTO #test1 
    FROM #test
    
    SELECT DISTINCT Col1,CAST('' AS VARCHAR(500)) AS Col2,0 Level 
    INTO #test2
    FROM #test1
    
    DECLARE @Recursion INT, @Init INT = 0;
    SELECT TOP (1) @Recursion = COUNT(1) FROM #test GROUP BY COl1 ORDER BY 1 DESC
    
    WHILE (@Init < @Recursion)
    BEGIN
    
    	UPDATE b
    	SET b.Col2 = CAST(CONCAT(b.Col2,',',a.Col2) AS VARCHAR(500))
    		,b.Level = @Init+1
    	FROM #test2 b
    	INNER JOIN #test1 a
    	ON a.Col1 = b.Col1
    	WHERE b.Level = @Init AND a.Rno = @Init+1
    
    	SET @Init = @Init+1
    END
    
    --Result
    SELECT 
    	Col1
    	,RIGHT(Col2,LEN(Col2)-1) Col2 
    FROM #test2

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 1, 2019 9:19 AM
  • thanks what about I have the following ? Can do it in the Azure datawarehouse ?

    Col1      Col2   Col3

    2019      JAN    10

    2019     FEB       20

    2019     MAR      30

    Pivot to

    Year      JAN      FEB     MAR

     2019      10      20      30

     

    Wednesday, October 2, 2019 1:27 AM
  • Hi

    If you are looking to enter into a new question, try closing the current thread and create a new thread as the initial query is already answered.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, October 2, 2019 6:57 AM
  • Hi

    Just checking in to see if issue resolved. If my earlier replies answers your query, do click “Mark as Answer” and Up-Vote for the same, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 7, 2019 4:04 PM