August 18, 2008 at 6:22 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 19, 2008 at 3:42 am
Hi
Here come an example which can solve your problem, hopefully. Peace 🙂
SELECT * FROM (
CASE WHEN ResursID IS NULL THEN 'EjPlan' ELSE 'Plan' END
SELECT 'Plan' AS plans, 0 AS Volym FROM DataBaseName
UNION ALL
SELECT 'EjPlan' AS plans, 0 AS Volym FROM DataBaseName
WHERE (dbo.GBR_SortimentGrupp.SortGruppniva3 <> '95')
) AS TempPivo1
PIVOT
(
SUM(Volym)
FOR
Plans
IN
(
[Plan], [EjPlan]
)
)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply