September 9, 2005 at 1:24 pm
I have a table that contains many rows where in order to get the required data you need to run a where clause to retrieve data. Now I need to create a report:
Scenario
current data storage:
ID Value
1 100
2 200
3 300
I need to display the data in the following manner
ID1 ID2 ID3 Total
100 200 300 600
I've done a UNION ALL and have yielded the following results:
ID1 ID2 ID3
100 0 0
0 200 0
0 0 300
How can I suppress the 0 values in order to get one row.
Thanks,
September 9, 2005 at 1:25 pm
use max or min... but I doubt that it'll solve your problem in this case since I assume you have more than 1 row to present.
September 9, 2005 at 2:10 pm
Can you use the CASE method for crosstab query?
September 9, 2005 at 2:18 pm
If your ID list is finite, then you can pivot the results like this:
create table pivot (ID int, Value Int)
go
insert into pivot values (1, 100)
insert into pivot values (2, 200)
insert into pivot values (3, 300)
insert into pivot values (4, 400)
go
select * from pivot
go
ID Value
----------- -----------
1 100
2 200
3 300
4 400
(4 row(s) affected)
SELECT DT.*, (DT.ID1 + DT.ID2 + DT.ID3 + DT.ID4) AS Total
FROM (SELECT
SUM(CASE ID WHEN 1 THEN Value ELSE 0 END) AS ID1,
SUM(CASE ID WHEN 2 THEN Value ELSE 0 END) AS ID2,
SUM(CASE ID WHEN 3 THEN Value ELSE 0 END) AS ID3,
SUM(CASE ID WHEN 4 THEN Value ELSE 0 END) AS ID4
FROM Pivot) DT
ID1 ID2 ID3 ID4 Total
----------- ----------- ----------- ----------- -----------
100 200 300 400 1000
(1 row(s) affected)
If your ID list is not finite, then you would have to create a UDF to do this.
September 9, 2005 at 2:35 pm
Yea, that one. 😉
September 12, 2005 at 8:46 am
Thanks guys,
Your input lit the lightbulb. this is what I have so far:
SELECT DISTINCT
prj.projectnumber,prj.dlhours,prj.dl,prj.nonDSMat
FROM
(SELECT projectnumber,
CASE account WHEN '14xxx' THEN normalhours ELSE 0 END AS 'dlhours',
CASE account
WHEN '14xxxx' THEN reclassified ELSE 0 END AS 'dl',
CASE glaccount
WHEN '14xxx' THEN reclassified ELSE 0 END AS 'nonDSMat'
FROM testproject_v) AS prj
This does what I need.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply