locked
pivot table RRS feed

  • Question

  • i have 2 tables:

    tb1

    id    name

    1      a

    2      b

    3      c

    ---

    tb2

    id1 is id of tb1

    id1       val

    1          ddd

    2          kkk

    1          mmm

    1          nnn

    ----------

    i need a query that convert tb2 rows to columns value

    with this report structure

    id     name       val1          val2           val3

    1       a             ddd          mmm         nnn

    2      b              kkk

    3      c


    • Edited by Ashkan209 Thursday, January 29, 2015 7:05 AM
    • Moved by Eric__Zhang Friday, January 30, 2015 8:00 AM T-SQL thread
    Thursday, January 29, 2015 7:05 AM

Answers

  • Any case, you need a dynamic method:

    create Table Tab1 ( Id int, name varchar(10))
    
    Insert into Tab1 Values(1,'a'),(2,'b'),(3,'c')
    
    create Table Tab2 ( Id int, det varchar(10))
    
    Insert into Tab2 Values(1,'ddd'),
    (2,'kkk'),
    (1,'mmm'),
    (1,'nnn'),
    (1,'ooooooo'),
    (1,'qqqq')
    
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
     
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           + QUOTENAME(rn)
    FROM (Select distinct Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS Courses
    
    SET @DynamicPivotQuery = 
      N'SELECT id,name AS name, ' + @ColumnName + '
        FROM
    (Select A.id,A.name,B.det,Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS SourceTable
    PIVOT
    (
    MIN(det)
    FOR rn IN (' + @ColumnName + ')) AS PVTTable'
    
    --Print @DynamicPivotQuery
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop table Tab1,Tab2
    

    With column names:

    create Table Tab1 ( Id int, name varchar(10))
    
    Insert into Tab1 Values(1,'a'),(2,'b'),(3,'c')
    
    create Table Tab2 ( Id int, det varchar(10))
    
    Insert into Tab2 Values(1,'ddd'),
    (2,'kkk'),
    (1,'mmm'),
    (1,'nnn'),
    (1,'ooooooo'),
    (1,'qqqq')
    
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName1 AS NVARCHAR(MAX)
    DECLARE @ColumnName2 AS NVARCHAR(MAX)
     
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName1= ISNULL(@ColumnName1 + ',','') 
           + QUOTENAME(rn)+ ' as Val'+Cast(rn as char(10)),
    	   @ColumnName2= ISNULL(@ColumnName2 + ',','') 
           + QUOTENAME(rn)
    FROM (Select distinct Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS Courses
    
    SET @DynamicPivotQuery = 
      N'SELECT id,name AS name, ' + @ColumnName1 + '
        FROM
    (Select A.id,A.name,B.det,Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS SourceTable
    PIVOT
    (
    MIN(det)
    FOR rn IN (' + @ColumnName2 + ')) AS PVTTable'
    
    Print @DynamicPivotQuery
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop table Tab1,Tab2
    
    


    • Edited by SQLZealots Thursday, January 29, 2015 7:34 AM
    • Proposed as answer by Eric__Zhang Friday, January 30, 2015 8:03 AM
    • Marked as answer by Kalman Toth Sunday, February 8, 2015 6:18 PM
    Thursday, January 29, 2015 7:30 AM
  • this

    SELECT 
    t1.id1, t1.name, t2.val1, t2.val2, t2.val3 FROM tb1 t1 INNER JOIN (SELECT id1, [1] AS Val1, [2] AS val2, [3] AS Val3 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id1 ORDER BY id1) AS Seq,* FROM tb2) t PIVOT(MAX(det) FOR Seq IN ([1],[2],[3]))p )t2 On t2.id1 = t1.id1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, January 29, 2015 7:32 AM
    • Marked as answer by Kalman Toth Sunday, February 8, 2015 6:18 PM
    Thursday, January 29, 2015 7:31 AM

All replies

  • If you have only 3 rows maximum for an id, you can use the below script, else, you may go with the dynamic pivot:

    create Table Tab1 ( Id int, name varchar(10))
    
    Insert into Tab1 Values(1,'a'),(2,'b'),(3,'c')
    
    create Table Tab2 ( Id int, det varchar(10))
    
    Insert into Tab2 Values(1,'ddd'),
    (2,'kkk'),
    (1,'mmm'),
    (1,'nnn')
    
    
    
    SELECT id,name AS name, 
     [1] as Val1, [2] Val2, [3] Val3
    FROM
    (Select A.id,A.name,B.det,Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS SourceTable
    PIVOT
    (
    MIN(det)
    FOR rn IN ([1], [2], [3])
    ) AS PivotTable;
    
    Drop table Tab1,Tab2
    
    

    Thursday, January 29, 2015 7:26 AM
  • Any case, you need a dynamic method:

    create Table Tab1 ( Id int, name varchar(10))
    
    Insert into Tab1 Values(1,'a'),(2,'b'),(3,'c')
    
    create Table Tab2 ( Id int, det varchar(10))
    
    Insert into Tab2 Values(1,'ddd'),
    (2,'kkk'),
    (1,'mmm'),
    (1,'nnn'),
    (1,'ooooooo'),
    (1,'qqqq')
    
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
     
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           + QUOTENAME(rn)
    FROM (Select distinct Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS Courses
    
    SET @DynamicPivotQuery = 
      N'SELECT id,name AS name, ' + @ColumnName + '
        FROM
    (Select A.id,A.name,B.det,Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS SourceTable
    PIVOT
    (
    MIN(det)
    FOR rn IN (' + @ColumnName + ')) AS PVTTable'
    
    --Print @DynamicPivotQuery
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop table Tab1,Tab2
    

    With column names:

    create Table Tab1 ( Id int, name varchar(10))
    
    Insert into Tab1 Values(1,'a'),(2,'b'),(3,'c')
    
    create Table Tab2 ( Id int, det varchar(10))
    
    Insert into Tab2 Values(1,'ddd'),
    (2,'kkk'),
    (1,'mmm'),
    (1,'nnn'),
    (1,'ooooooo'),
    (1,'qqqq')
    
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName1 AS NVARCHAR(MAX)
    DECLARE @ColumnName2 AS NVARCHAR(MAX)
     
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName1= ISNULL(@ColumnName1 + ',','') 
           + QUOTENAME(rn)+ ' as Val'+Cast(rn as char(10)),
    	   @ColumnName2= ISNULL(@ColumnName2 + ',','') 
           + QUOTENAME(rn)
    FROM (Select distinct Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS Courses
    
    SET @DynamicPivotQuery = 
      N'SELECT id,name AS name, ' + @ColumnName1 + '
        FROM
    (Select A.id,A.name,B.det,Row_number()Over(partition by A.id Order by B.det asc) Rn From Tab1 A
    Left Join Tab2 B on A.ID = B.ID ) AS SourceTable
    PIVOT
    (
    MIN(det)
    FOR rn IN (' + @ColumnName2 + ')) AS PVTTable'
    
    Print @DynamicPivotQuery
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop table Tab1,Tab2
    
    


    • Edited by SQLZealots Thursday, January 29, 2015 7:34 AM
    • Proposed as answer by Eric__Zhang Friday, January 30, 2015 8:03 AM
    • Marked as answer by Kalman Toth Sunday, February 8, 2015 6:18 PM
    Thursday, January 29, 2015 7:30 AM
  • this

    SELECT 
    t1.id1, t1.name, t2.val1, t2.val2, t2.val3 FROM tb1 t1 INNER JOIN (SELECT id1, [1] AS Val1, [2] AS val2, [3] AS Val3 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id1 ORDER BY id1) AS Seq,* FROM tb2) t PIVOT(MAX(det) FOR Seq IN ([1],[2],[3]))p )t2 On t2.id1 = t1.id1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, January 29, 2015 7:32 AM
    • Marked as answer by Kalman Toth Sunday, February 8, 2015 6:18 PM
    Thursday, January 29, 2015 7:31 AM
  • The table tb1 and tb2 example are.and The number of rows in the tb2 may be high
    Also, do not allow the creation Table
    Thursday, January 29, 2015 7:31 AM
  • The table tb1 and tb2 example are.and The number of rows in the tb2 may be high
    Also, do not allow the creation Table
    tab1 and tab2 are example tables.Just replace the tables with your actual tables and use the dynamic way as in the above post. Try and let us know for any issues.
    Thursday, January 29, 2015 7:35 AM