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! ☺

Lukasz Szozda · Accepted Answer · 2016-03-11 10:53:45Z

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

answered Mar 11, 2016 at 10:53

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

404notfound Over a year ago

wow. i am a newbie in sql. thanks! one more thing, what if my HEADING COLUMN does not contains rtf then the RTF COLUMN should not be displayed

Lukasz Szozda Over a year ago

@404notfound Then you probably want dynamic-SQL. If you are newbie I would stick with this solution and just ignore RTF (full of zeros) or just use SELECT [File], [XML], [PDF] FROM ...

Collectives™ on Stack Overflow

Add new column and assign values from existing table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related