CODEWITHSUNDEEP
sqlsql-server
frar
frar

Reputation: 21

Convert row result to columns

Hi I have an example code and the desired outcome, i have tried to use Pivot and left join same table and looked at similar q here but I dont get the result i want so I am asking the experts for help :) : 

DECLARE @temp TABLE (id INT, typeID INT)

INSERT INTO @temp VALUES(1,1)
INSERT INTO @temp VALUES(1,2)
INSERT INTO @temp VALUES(1,3)
INSERT INTO @temp VALUES(1,4)
INSERT INTO @temp VALUES(1,5)
INSERT INTO @temp VALUES(2,1)
INSERT INTO @temp VALUES(2,2)
INSERT INTO @temp VALUES(2,3)
INSERT INTO @temp VALUES(3,5)


 SELECT * FROM @temp

--desired result
----------------------------------------------------------
 [id]  [typeID1]  [typeID2]  [typeID3]  [typeID4]  [typeID5]
 1      1           1       1           1           1
 2      1           1       1   
 3                                                  1   
 ----------------------------------------------------------

Upvotes: 2

Views: 69

Answers (3)

Fmanin
Fmanin

Reputation: 575

Please follow this: https://msdn.microsoft.com/en-us/library/hh231515.aspx

In my opinion it is really helpful if you learn how it behaves:

DECLARE @temp TABLE (id INT, typeID INT)

INSERT INTO @temp VALUES(1,1)
INSERT INTO @temp VALUES(1,2)
INSERT INTO @temp VALUES(1,3)
INSERT INTO @temp VALUES(1,4)
INSERT INTO @temp VALUES(1,5)
INSERT INTO @temp VALUES(2,1)
INSERT INTO @temp VALUES(2,2)
INSERT INTO @temp VALUES(2,3)
INSERT INTO @temp VALUES(3,5)

SELECT ID,
[1] as Type1, [2] as Type2, [3] as Type3, [4] as Type4, [5] as Type5
FROM
(SELECT ID, typeID 
    FROM @temp) AS SourceTable
PIVOT
(
COUNT(TYPEID)
FOR TYPEID IN ([1], [2], [3], [4],[5])
) AS PivotTable

Upvotes: 0

gofr1
gofr1

Reputation: 15977

Another way with PIVOT and dynamic SQL (as we dont know how many typeIDs presents in the table:

USE tempdb 

CREATE TABLE #temp (id INT, typeID INT)

INSERT INTO #temp VALUES
(1,1),(1,2),(1,3),(1,4),(1,5),(2,1),(2,2),(2,3),(3,5)

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = COALESCE(@columns,'') + ',[typeID'+CAST(typeID as nvarchar(max))+']'
FROM #temp
GROUP BY typeID

SET @sql = N'
SELECT id'+@columns+'
FROM (
SELECT  ''typeID''+CAST(typeID as nvarchar(max)) as [types],
        id
FROM #temp) as t
PIVOT (
    COUNT([types]) FOR [types] IN ('+STUFF(@columns,1,1,'')+')
) as unpvt'

EXEC sp_executesql @sql

DROP TABLE #temp

Output:

id  typeID1 typeID2 typeID3 typeID4 typeID5
1   1       1       1       1       1
2   1       1       1       0       0
3   0       0       0       0       1

Upvotes: 0

Serg
Serg

Reputation: 22811

This code will run on most SQL DBMSes . 

select id, 
    max(case typeID when 1 then 1 end) as typeID1,
    max(case typeID when 2 then 1 end) as typeID2,
    max(case typeID when 3 then 1 end) as typeID3,
    max(case typeID when 4 then 1 end) as typeID4,
    max(case typeID when 5 then 1 end) as typeID5
from @temp
group by id

Upvotes: 1

Related Questions