Covert a duplicate Row value into column using pivot table

Question

Here is my stored procedure query to fetch data in first table.

SELECT [Item] = t3.Item,
[Name] = t2.Name,
[Value] = t1.value

INTO #Result

FROM table1 t1
INNER JOIN table2 t2 ON t2.IsDeleted = 0
INNER JOIN table3 t3 ON t3.IsDeleted = 0 AND t3.Item_ID = @Id
WHERE t1.Item_ID = @Id 

GROUP BY 
t1.value,
t2.Name,
t3.Item

I have following Data in a temp table.

|  Item |  Name  | Value |
--------------------------
| item1 | Name 1 |   2   |
| item2 | Name 1 |   4   |
| item3 | Name 1 |   5   |
| item1 | Name 2 |   6   |
| item2 | Name 2 |   3   |
| item3 | Name 2 |   1   |
| item1 | Name 3 |   7   |
| item2 | Name 3 |   4   |
| item3 | Name 3 |   2   |

I want Name 1, Name 2, Name 3 as column and their value against the respective item.Data in table is dynamic.There can be any number of Items and any number of Name. For every Name and Item their is a value which can be a single digit number. Items are same for every Name Thanks

I need something like below.

|  Item | Name 1 | Name 2 | Name 3|
------------------------------------
| item1 |    2   |    6   |   7   |
| item2 |    4   |    3   |   4   |
| item3 |    5   |    1   |   2   |

As I need to use it in Stored Procedure. If you want I can post my stored procedure. — Renascent, Feb 15 '17 at 08:42
Tag the dbms you're using. Most products have their own, non-ANSI SQL, versions of stored procedures. — jarlh, Feb 15 '17 at 08:47

score 1 · Accepted Answer · answered Feb 17 '17 at 05:39

This Worked well for me. :)

IF OBJECT_ID('TEMPDB.dbo.##FinalResult ') IS NOT NULL DROP TABLE ##FinalResult 

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Name])     
FROM (SELECT DISTINCT [Name] FROM #Result) AS ##FinalResult 

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT [Item], ' + @ColumnName + '
    INTO ##FinalResult 
    FROM #Result 
    PIVOT(MAX([Value])
    FOR [Name] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

SELECT * FROM ##FinalResult

score 0 · Answer 2 · edited May 23 '17 at 10:29

Use pivot

declare @T table
(
    item varchar(5),
    name varchar(6),
    value smallint
)

insert into @T
select 'item1' , 'Name 1' , 2 union
select 'item2' , 'Name 1' , 4 union
select 'item3' , 'Name 1' , 5 union
select 'item1' , 'Name 2' , 6 union
select 'item2' , 'Name 2' , 3 union
select 'item3' , 'Name 2' , 1 union
select 'item1' , 'Name 3' , 7 union
select 'item2' , 'Name 3' , 4 union
select 'item3' , 'Name 3' , 2 

select * 
from
(
    select * from @T
) p
pivot
(
    sum(value) for name in ([Name 1], [Name 2], [Name 3])
) pvt

If your names are not constants and may differ, you should create dynamic sql query (here you can find an example of dynamic query).

Covert a duplicate Row value into column using pivot table

2 Answers2