Split value from field into columns

Question

I have a table with the following data:

subitem, subitempart, subitemvalue
 'Badge", 'Issued to', 'abc'
 'Badge', 'Located at', 'bcd'
 'Badge, 'signed for', 'def'
 'key', 'number', '123'
 'key', 'Issued to', 'abc'

The subitempart names are from a separate table with two fields: [code] and [description]. A subitem can have 1 to 20 subitemparts, it varies per item.

My client wants to see this as followed:

[Subitem], [Issued to], [Located at], [Signed for], [Number]
'Badge'  , 'abc'      , 'bcd'       , 'def'       , NULL
'key'    , 'abc'      , NULL        , NULL        , '123'

So what I want to do, is put all subitemparts available to columns, then put the values in the right fields and then remove the columns that are empty on each row.

Is this possible in MS SQL and if yes, how?

thanks in advance for any help.

rg. Eric

Hi Eric, try to give a look at this http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Raphael, Nov 04 '13 at 13:30
Hi, thanks. I did in fact look at that page, but couldn't relate it to my specific problem. — Eric, Nov 04 '13 at 15:37
Wait, I write you an example to show you how to relate it to your problem ;) — Raphael, Nov 05 '13 at 06:32

score 0 · Answer 1 · answered Nov 04 '13 at 13:34

Looks like you're trying to pivot your results. One option is to use the PIVOT command. I understand it better using MAX with CASE though:

select subitem,
   max(case when subitempart = 'Issued to' then subitemvalue end) IssuedTo,
   max(case when subitempart = 'Located at' then subitemvalue end) LocatedAt,
   max(case when subitempart = 'signed for' then subitemvalue end) SignedFor,
   max(case when subitempart = 'number' then subitemvalue end) number
from yourtable
group by subitem

SQL Fiddle Demo

Hi, thanks, but that the table of subitemparts can be changed by adding new ones or deleting old ones. I don't want to edit the query each time that happens, so I need a more dynamic solution. — Eric, Nov 04 '13 at 15:37

score 0 · Answer 2 · answered Nov 05 '13 at 06:34

0

You can you a pivot table, in sql it's not that difficult.

SELECT * FROM item
PIVOT (max(subitemvalue) 
    for subitempart in ([Issued to],[Located at],[signed to],[number])) as items

hope this helps

answered Nov 05 '13 at 06:34

Raphael

1,677
1
15
23

Thank you, but how can I make it more dynamic. I want the query also to work when a user adds a new subitempart. – Eric Nov 06 '13 at 12:05
1

it's a bit tricky check out this post http://stackoverflow.com/questions/11985796/sql-server-pivot-dynamic-columns-no-aggregation this is how it work, We use this method to do the dynamic pivot – Raphael Nov 06 '13 at 12:18

Split value from field into columns

2 Answers2