August 18, 2008 at 6:27 am
Hello,
Please can someone help me with creating a view that pivots text values to put each new value a separate column. This is similar to a crosstab, but the intersections are not numeric values.
The method I have tried, which works, is using correlated sub-queries with CASE method and row numbers partitioned by the primary key fields to identify each instance. Although this works, I'm sure there must be a better way to do this!
The key columns in my table are LCWLOC, LCLPID and LCCPNT and I want to end up with one record for each LCWLOC, LCLPID. The column to "flatten" is LCCPNT (component). The query must also exclude certain rows (Modified_Type = 'D') and give the total number of components.
A shortened version of my code is (the full version has more columns and creates 10 separate component columns):
select
ccls.LCWLOC,
ccls.LCLPID,
CASE WHEN ccls1.row_number = 1 THEN ccls1.LCCPNT ELSE NULL END as component1_cd,
CASE WHEN ccls2.row_number = 2 THEN ccls2.LCCPNT ELSE NULL END as component2_cd,
CASE WHEN ccls3.row_number = 3 THEN ccls3.LCCPNT ELSE NULL END as component3_cd,
ccls.compnts_qty
from (select
LCWLOC,
LCLPID,
count(*) as compnts_qty
from dbo.LICCLS
where Modified_Type <> 'D'
group by
LCWLOC,
LCLPID) AS ccls LEFT JOIN
(select *, row_number() over(partition by LCWLOC,LCLPID order by LCWLOC,LCLPID,LCCPNT) as row_number
from dbo.LICCLS where Modified_Type <> 'D') AS ccls1
on ccls.LCWLOC = ccls1.LCWLOC and ccls.LCLPID = ccls1.LCLPID and ccls1.row_number = 1 LEFT JOIN
(select *, row_number() over(partition by LCWLOC,LCLPID order by LCWLOC,LCLPID,LCCPNT) as row_number
from dbo.LICCLS where Modified_Type <> 'D') AS ccls2
on ccls.LCWLOC = ccls2.LCWLOC and ccls.LCLPID = ccls2.LCLPID and ccls2.row_number = 2 LEFT JOIN
(select *, row_number() over(partition by LCWLOC,LCLPID order by LCWLOC,LCLPID,LCCPNT) as row_number
from dbo.LICCLS where Modified_Type <> 'D') AS ccls3
on ccls.LCWLOC = ccls3.LCWLOC and ccls.LCLPID = ccls3.LCLPID and ccls3.row_number = 3
I'd appreciate any suggestions to try!
August 18, 2008 at 7:33 am
Please don't cross post.
Answer here: http://www.sqlservercentral.com/Forums/Topic554245-147-1.aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply