Add new column and assign values from existing table

Question

I have a table like this:

REFERENCE     FILE             FILESIZE    HEADING
--------------------------------------------------
ref001        abc123.zip       0            
ref001        def123.sgm       46661       pdf
ref001        ghi123.xml       1159        xml
ref001        jkl456.zip       0         
ref001        mno456.sgm       22852       rtf
ref001        pqr456.xml       1181        xml
ref001        stu789.zip       0
ref001        vwx789.sgm       5959        pdf
ref001        yza789.xml       1162        xml

How can I create new columns based on the FILESIZE AND HEADING COLUMN from above? Like this:

FILE             PDF         RTF        XML
--------------------------------------------
abc123.zip       0           0          0 
def123.sgm       46661       0          0
ghi123.xml       0           0          1159  
jkl456.zip       0           0          0
mno456.sgm       0           22852      0
pqr456.xml       0           0          1181
stu789.zip       0           0          0
vwx789.sgm       5959        0          0
yza789.xml       0           0          1162  

Thanks for any help! ☺

Answer 1

You could use PIVOT:

SELECT [File],
       COALESCE([PDF],0) AS [PDF],
       COALESCE([XML],0) AS [XML],
       COALESCE([RTF],0) AS [RTF]
FROM #tab
PIVOT
(
  MAX(FILESIZE) FOR HEADING IN ([PDF],[XML],[RTF])
) AS pvt

LiveDemo

or simple CASE:

SELECT [File],
       [XML] = CASE WHEN Heading = 'xml' THEN FileSize ELSE 0 END,
       [PDF] = CASE WHEN Heading = 'pdf' THEN FileSize ELSE 0 END,
       [RTF] = CASE WHEN Heading = 'rtf' THEN FileSize ELSE 0 END
FROM #tab

LiveDemo2