July 10, 2008 at 2:17 pm
I have a table with a number of columns, which each row being for a different "codetype", tied together by a common key. Depending on the codetype value, different columns in the table will have data. I would like to take these multiple rows, and merge them together into a single row with the combined data. I hope this example shows it:
My data table
certid codetype polnum
670624 AUTOLI 123456
670624 GENLIA abcdef
I want on a single row to be returned this:
certid GLPOLNUM ALPOLNUM
670624 abcdef 123456
I thought this would work:
select '' as GLPOLNUM, strpolicynum as ALPOLICYNUM
from coverages
where lngcertid_pk=670624
and strcovcode='AUTOLI'
UNION
select strpolicynum as GLPOLNUM, '' as ALPOLICYNUM
from coverages
where lngcertid_pk=670624
and strcovcode='GENLIA'
but it still returns two offsetting rows:
certid GLPOLNUM ALPOLNUM
670624 123456
670624 abcdef
What am I doing wrong? I thought I could use UNION to do this, but apparently not. What else should I try? Thanks!
July 10, 2008 at 10:09 pm
Union combines multiple result sets into one result set. It does not combine multiple records into single records.
There are other methods to combine multiple records into single records, the most obvious being Group By:
Select certid
, MAX( Case strcovcode When 'AUTOLI' Then strpolicynum Else '' End ) as ALPOLICYNUM
, MAX( Case strcovcode When 'GENLIA' Then strpolicynum Else '' End ) as GLPOLNUM
From coverages
Group By sertid
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 10, 2008 at 10:30 pm
I think PIVOT will do the job...
Try this,
SELECT * FROM
(
SELECT c.certID, c.codetype, c.polnum FROM coverages c
WHERE c.certID = 670624 and c.codetype in ('AUTOLI','GENLIA')
) m
PIVOT (MAX(polnum) FOR codetype IN ([AUTOLI],[GENLIA]))p
Atif Sheikh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply