different arranged results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

different arranged results

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2010-06-11 : 00:22:35

Hello everyone.

while having the following sintax:

SELECT SC01023 (datediff(day,PC41015,PC42017)) SUM (CASE WHEN PC42009 = '6' THEN PC42011*1000 ELSE PC42011 END)
from SC010100,PC410100,PC420100
WHERE PC41001 = PC42001 AND
PC42005 LIKE 'MP%' AND SC01001 = PC42005 AND PC42017 BETWEEN '2009-06-01' AND '2010-05-31'
group BY SC01023, PC41015, PC42017
ORDER BY SC01023, PC42017 DESC

i get the following results :

a1 0 25.00000000
c3 12 500.00000000
c3 9 40.00000000
c3 15 100.00000000
c3 7 20.00000000
c3 0 0.00000000
b4 8 100.00000000
x8 5 5000.00000000
x8 15 2475.00000000
x8 7 1000.00000000
x8 13 600.00000000
x8 0 100.00000000

i need the following output:

a1 0 null null null null 25 null null null null
c3 12 9 15 7 0 500 40 100 20 0
b4 8 null null null null 100 null null null null
x8 5 15 7 13 0 5000 2475 1000 600 100

and so on.

is there any way to do this?

thanks in advance.

tempus
Starting Member

47 Posts

Posted - 2010-06-11 : 02:15:09

A slight modification to the script. i managed to get my third column as i wanted, there are 12 of them. now i need the second one like the third one.

the new script:

SELECT SC01023 clasa,
(datediff(day,PC41015,PC42017)) lead_time,

SUM (CASE WHEN SC07002 BETWEEN '2009-06-01' AND '2009-06-30' THEN SC07004 ELSE 0 END) c1,
SUM (CASE WHEN SC07002 BETWEEN '2009-07-01' AND '2009-07-31' THEN SC07004 ELSE 0 END) c2,
SUM (CASE WHEN SC07002 BETWEEN '2009-08-01' AND '2009-08-31' THEN SC07004 ELSE 0 END) c3,
SUM (CASE WHEN SC07002 BETWEEN '2009-09-01' AND '2009-09-30' THEN SC07004 ELSE 0 END) c4,
SUM (CASE WHEN SC07002 BETWEEN '2009-10-01' AND '2009-10-31' THEN SC07004 ELSE 0 END) c5,
SUM (CASE WHEN SC07002 BETWEEN '2009-11-01' AND '2009-11-30' THEN SC07004 ELSE 0 END) c6,
SUM (CASE WHEN SC07002 BETWEEN '2009-12-01' AND '2009-12-31' THEN SC07004 ELSE 0 END) c7,
SUM (CASE WHEN SC07002 BETWEEN '2010-01-01' AND '2010-01-31' THEN SC07004 ELSE 0 END) c8,
SUM (CASE WHEN SC07002 BETWEEN '2010-02-01' AND '2010-02-28' THEN SC07004 ELSE 0 END) c9,
SUM (CASE WHEN SC07002 BETWEEN '2010-03-01' AND '2010-03-31' THEN SC07004 ELSE 0 END) c10,
SUM (CASE WHEN SC07002 BETWEEN '2010-04-01' AND '2010-04-30' THEN SC07004 ELSE 0 END) c11,
SUM (CASE WHEN SC07002 BETWEEN '2010-05-01' AND '2010-05-31' THEN SC07004 ELSE 0 END) c12

from SC010100,PC410100,PC420100, SC070100
WHERE PC41001 = PC42001 AND PC42005 LIKE 'MP%' AND
SC01001 = PC42005 AND PC42017 BETWEEN '2009-06-01' AND '2010-05-31' AND
SC07003 = SC01001 AND SC07007 LIKE '6%' AND SC07001 = '01'
group BY SC01023, PC41015, PC42017
ORDER BY SC01023, PC42017 DESC

the datediff results are independent of the month so there can be more than 1 result in 1 month or 0 results. where 0 results , a null can apear if possible.

a1 0 null null null null c1 c2 ... c12
c3 12 9 15 7 0 c1 c2 ... c12
b4 8 null null null null c1 c2 ... c12
x8 5 15 7 13 0 c1 c2 ... c12

thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources