February 5, 2008 at 2:33 am
Hello,
I have a need to present a different orientation of data, and I would normally do this with CASE. I've tried to go that route in this instance, but it hasn't worked.
The following query expresses the data as it currently is:
SELECT TOP 10
Prod,
Acct,
TC,
SubmitRep1,
SubmitRep2,
SubmitRep3,
Percent1,
Percent2,
Percent3
FROM
dbo.tbl_CYProcessedSales
WHERE
Proj IN (100,200)
AND Percent1 < 80
AND NOT SubmitRep3 = ''
AND Prod = 'RETA'
The result looks like this:
Prod Acct TC SubmitRep1 SubmitRep2 SubmitRep3 Percent1 Percent2 Percent3
RETA PA0065 T2 OJOYNER CAPARRSH SBEVERLY 34 33 33
I need for the result to come out like this:
Prod Acct TC Rep Percentage
RETA PA0065 T2 OJOYNER 34
RETA PA0065 T2 CAPARRSH 33
RETA PA0065 T2 SBERVERLY 33
Do I want to use CASE here, or a cross join?
Thank you for your help!
CSDunn
February 5, 2008 at 8:55 am
SELECT TOP 10
Prod,
Acct,
TC,
SubmitRep1,
Percent1,
FROM
dbo.tbl_CYProcessedSales
WHERE
Proj IN (100,200)
AND Percent1 < 80
AND NOT SubmitRep3 = ''
AND Prod = 'RETA'
UNION
SELECT TOP 10
Prod,
Acct,
TC,
SubmitRep2,
Percent2,
FROM
dbo.tbl_CYProcessedSales
WHERE
Proj IN (100,200)
AND Percent1 < 80
AND NOT SubmitRep3 = ''
AND Prod = 'RETA'
UNION
SELECT TOP 10
Prod,
Acct,
TC,
SubmitRep3,
Percent3
FROM
dbo.tbl_CYProcessedSales
WHERE
Proj IN (100,200)
AND Percent1 < 80
AND NOT SubmitRep3 = ''
AND Prod = 'RETA'
February 5, 2008 at 10:26 am
SELECT TOP 30
    S.Prod
    ,S.Acct
    ,S.TC
    ,CASE N.N
        WHEN 1 THEN S.SubmitRep1
        WHEN 2 THEN S.SubmitRep2
        ELSE S.SubmitRep3
    END AS Rep
    ,CASE N.N
        WHEN 1 THEN S.Percent1
        WHEN 2 THEN S.Percent2
        ELSE S.Percent3
    END AS Percentage
FROM dbo.tbl_CYProcessedSales S
    CROSS JOIN -- make 3 rows for each original row.
    (
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3
    ) N (N) -- or use number/tally table
WHERE S.roj IN (100,200)
    AND S.Percent1 < 80
    AND NOT S.SubmitRep3 = ''
    AND S.Prod = 'RETA'
February 5, 2008 at 12:22 pm
;with
Rep1 (Prod, Acct, TC, SubmitRep1, Percent1, ID, Seq) as
(select Prod, Acct, TC, SubmitRep1, Percent1,
row_number() over (order by Prod, Acct, TC, SubmitRep1, Percent1, 1)
from dbo.tbl_CYProcessedSales T1
WHERE
Proj IN (100,200)
AND Percent1 < 80
AND NOT SubmitRep3 = ''
AND Prod = 'RETA'),
Rep2 (Prod, Acct, TC, SubmitRep2, Percent2, ID, Seq) as
(select T2.Prod, T2.Acct, T2.TC, SubmitRep2, Percent2, Rep1.ID, 2
from dbo.tbl_CYProcessedSales T2
inner join Rep1 on T2.prod = rep1.prod
and t2.acct = rep1.prod
and t2.tc = rep1.tc
and t2.submitrep1 = rep1.submitrep1
and t2.percent1 = rep1.percent1
WHERE
Proj IN (100,200)
AND T2.Percent1 < 80
AND NOT SubmitRep3 = ''
AND T2.Prod = 'RETA'),
Rep3 (Prod, Acct, TC, SubmitRep3, Percent3, ID, Seq) as
(select T3.Prod, T3.Acct, T3.TC, SubmitRep3, Percent3, Rep1.ID, 3
from dbo.tbl_CYProcessedSales T3
inner join Rep1 on T3.prod = rep1.prod
and t3.acct = rep1.prod
and t3.tc = rep1.tc
and t3.submitrep1 = rep1.submitrep1
and t3.percent1 = rep1.percent1
WHERE
Proj IN (100,200)
AND T3.Percent1 < 80
AND NOT SubmitRep3 = ''
AND T3.Prod = 'RETA')
select top 10 *
from rep1
union all
select top 10 *
from rep2
union all
select top 10 *
from rep3
order by ID, Seq
That will guarantee that you get the same rows in the same sequence (first 3 will be from the first row in the main table, next 3 from the second row, etc.). It will also force the SubmitRep1 to be before the SubmitRep2, which will be before the SubmitRep3, in each sequence.
Without the Order By, you might get a mix of rows.
If you have a column within the table that you can sort with, you can use that instead. From the description, I wasn't sure.
(Of course, the better thing to do with this kind of data, in almost all cases, would be to normalize the table. The SubmitReps and their percentages should be rows in a sub-table, not columns in the parent table. The way it is right now, it looks like a violation of normal form. Normalizing it would make such a query much easier to write, and would make it run faster, and be more accurate in most cases.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply