Need to transpose Table Data with adding 0 for non existing column

Question

I have data as follows -

FieldValue         FiledID    UnqiueID
Name1                 13          1
Address1              14          1
NAme2                 13          2
Address2              14          2
Name3                 13          3
Address3              14          3
Date1                 15          3
Date2                 16          3
Date3                 17          3

I would like to transpose it. My outcome will be as follows -

Name1 Address1 0 0 0
Name2 Address2 0 0 0
Name3 Address3 Date1 Date2 Date3

I was trying to do it through PIVOT, but no success. Like to achieve it through SQL not preferring any SP.

score 0 · Answer 1 · answered Jun 08 '14 at 18:21

0

I would be inclined to do this with conditional aggregation instead of pivot:

select max(case when FieldId = 13 then FieldValue end) as Name,
       max(case when FieldId = 14 then FieldValue end) as Address,
       max(case when FieldId = 15 then FieldValue end) as Date1,
       max(case when FieldId = 16 then FieldValue end) as Date2,
       max(case when FieldId = 17 then FieldValue end) as Date3
from table t
group by uniqueid;

answered Jun 08 '14 at 18:21

Gordon Linoff

1,242,037
58
646
786

But in this scenario, my FieldValue is not constant and it's varying. As you can see that Name1, Name2, Address1, Address2 etc – user1841927 Jun 09 '14 at 18:46
@user1841927 . . . This query should return the values that you are looking for for each unique id. – Gordon Linoff Jun 09 '14 at 19:40

Need to transpose Table Data with adding 0 for non existing column

1 Answers1