Select SQL Row value as columns like pivot

Question

I have a table

 ID   TYPE  VALUE
1001   A       3
1002   A       4
1003   B       1
1002   B       5
1001   C       9
1002   A       4
1003   c       7

The output I need to get should look like this

 ID   TYPE A   TYPE B   TYPE C
1001   3         0        9
1002   8         5        0
1003   0         1        7

How can I do that?

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "_SQL_" is just a query language, not the name of a specific database product. — , Oct 27 '17 at 10:31
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Valerica, Oct 27 '17 at 10:32
You haven't even asked a question. You are supposed to come here with a concrete problem. Your only "problem", however, seems to be that you don't bother to look at the manual and write the query. We cannot help you with that. — Thorsten Kettner, Oct 27 '17 at 10:35

Yogesh Sharma · Accepted Answer · 2017-10-27T11:03:17.677

You could try below SQL Query if don't want go Dynamic or Pivot :

SELECT id,
       ISNULL(SUM(CASE(type)
                      WHEN 'A'
                      THEN VALUE
                  END), 0) [TYPE A],
       isnull(SUM(CASE(type)
                      WHEN 'B'
                      THEN VALUE
                  END), 0) [TYPE B],
       isnull(SUM(CASE(type)
                      WHEN 'C'
                      THEN VALUE
                  END), 0) [TYPE C]
FROM <table_name>
GROUP BY id;

Result :

ID      TYPE A  TYPE B  TYPE C 
1001    3       0       9
1002    8       5       0
1003    0       1       7

score 1 · Answer 2 · answered Oct 27 '17 at 10:40

1

You could try:

   Select *

    From(
        'Insert Your Original Query Here or Table Name'    ) P

    Pivot   
        (Sum(Value)
            For TYPE in ([Type A],[Type B],[Type C]) As pvt
    Order By
        ID

answered Oct 27 '17 at 10:40

PeterH

975
2
14
36

score 0 · Answer 3 · answered Oct 27 '17 at 11:12

Try this dynamic sql

DECLARE @SQL nvarchar(max),
        @Columns nvarchar(max),@ColumnsType nvarchar(max)


SELECT @Columns=STUFF((SELECT DISTINCT ', '+QUOTENAME([TYPE])  FROM #table FOR XML PATH ('')),1,2,'')

SELECT @ColumnsType=STUFF((SELECT DISTINCT ', '+'ISNULL ('+QUOTENAME([TYPE])+','+'''0'''+') AS '+ 'Type'+[TYPE] FROM #table FOR XML PATH ('')),1,2,'')


SET @SQL='
SELECT ID,'+@ColumnsType+' From
(
SELECT * FROM #table
)
AS Src
    PIVOT
    (
    SUM(VALUE) FOR [TYPE] IN ('+@Columns+')
    )as Pvt
'
Print @SQL
EXEC (@SQL)

Result

ID      TypeA   TypeB   TypeC
-------------------------------
1001    3        0       9
1002    8        5       0
1003    0        1       7

Select SQL Row value as columns like pivot

3 Answers3