pivot table

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.