Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my database I have a set of custom field values that can be setup by the customer. They can create labels for these fields and then assign values which are linked back to particular tables in the database. I need to be able to report this information such that the labels appear as columns and the values are listed below. I could do this with a PIVOT function except that the information in the values is not necessarily aggregatable. (I'm making up new words) I'm using the following code for testing.

create table custlabel (custlabelid int identity(1,1) not null, custlabel varchar(50))
CREATE table custvalue (custvalueid int identity(1,1) not null, custlabelid int not null, custvalue varchar(50), linkageid int not null)
create table linkage (linkageid int identity(1,1), blah varchar(50))

INSERT into linkage(blah)
values('test'), ('test2'), ('test3')

INSERT INTO custlabel(custlabel)
values('info1'), ('info2'), ('info3'), ('info4'), ('info5')

INSERT INTO custvalue(custlabelid, custvalue, linkageid)
VALUES(1, 'info1 value1', 1),
(1, 'info1 value2', 1),
(1, 'info1 value3', 1),
(2, 'info2 value1', 1),
(2, 'info2 value2', 1),
(2, 'info2 value3', 1),
(2, 'info2 value4', 1),
(3, 'info3 value1', 1),
(3, 'info3 value2', 1),
(3, 'info3 value3', 1),
(4, 'info4 value1', 1)

What I need to see come back is

blah, info1, info2, info3, info4, info5
test, info1 value1, info2 value1, info3 value1, NULL, NULL
test, info1 value2, info2 value2, info3 value2, NULL, NULL
test, info1 value3, info2 value3, info3 value3, NULL, NULL
test, info4 value1, NULL, NULL, NULL, NULL
share|improve this question
    
Can you show what output you expect? Tough to coalesce the word problem with a bunch of inserts. –  Aaron Bertrand Oct 1 '13 at 14:16
    
@AaronBertrand yeah sorry I'll get that up in a min –  Lumpy Oct 1 '13 at 14:18

1 Answer 1

up vote 2 down vote accepted

You should still be able to get the result that you want using the PIVOT function. If your values that you need to aggregate are a string, then you would use either min or max to get the final result.

Based on your sample data and the desired result, you will need to generate a distinct value that will be used when grouping in the PIVOT. I would use row_number() to create a sequenced number for each blah and custlabel, this will allow you to return multiple rows for each blah.

If you have a limited number of custlabels, then you can hard-code the query:

select blah, info1, info2, info3, info4, info5
from
(
  select cl.custlabel,
    cv.custvalue,
    l.blah
    , row_number() over(partition by l.blah, cl.custlabel
                      order by cl.custlabel) seq
  from custlabel cl
  inner join custvalue cv
    on cl.custlabelid = cv.custlabelid
  inner join linkage l
    on cv.linkageid = l.linkageid
) d
pivot
(
  max(custvalue)
  for custlabel in (info1, info2, info3, info4, info5)
) piv;

See SQL Fiddle with Demo.

But if you are going to have an unknown number of custlabels, then you will want to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(custlabel) 
                    from custlabel
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT blah, ' + @cols + ' 
            from 
            (
              select cl.custlabel,
                cv.custvalue,
                l.blah
                , row_number() over(partition by l.blah, cl.custlabel
                                  order by cl.custlabel) seq
              from custlabel cl
              inner join custvalue cv
                on cl.custlabelid = cv.custlabelid
              inner join linkage l
                on cv.linkageid = l.linkageid
            ) x
            pivot 
            (
                max(custvalue)
                for custlabel in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo

Both versions will give a result:

| BLAH |        INFO1 |        INFO2 |        INFO3 |        INFO4 |  INFO5 |
| test | info1 value1 | info2 value1 | info3 value1 | info4 value1 | (null) |
| test | info1 value2 | info2 value2 | info3 value2 |       (null) | (null) |
| test | info1 value3 | info2 value3 | info3 value3 |       (null) | (null) |
| test |       (null) | info2 value4 |       (null) |       (null) | (null) |
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.