Help to avoid Nulls and show in single row

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)

Help to avoid Nulls and show in single row

Author

Topic

learntsql

524 Posts

Posted - 2009-12-08 : 07:16:41

Hi All..
My data and output is as follows which i have to bind to report.
Job ID Phase Value1 Value2 Value3 Value4
1 1 Phase1 Info V1 NULL NULL NULL
1 2 Phase1 Done Null V2 V2 V3
1 3 Phase2 Info V1 NULL NULL NULL
1 4 Phase2 Done Null V2 V2 V3
1 5 Phase3 Info V1 NULL NULL NULL
1 6 Phase3 Done Null V2 V2 V3
1 7 Phase4 Info V1 NULL NULL NULL
1 8 Phase4 Done Null V2 V2 V3
2 9 Phase1 Info V1 NULL NULL NULL
2 10 Phase1 Done Null V2 V2 V3
2 11 Phase2 Info V1 NULL NULL NULL
2 12 Phase2 Done Null V2 V2 V3
2 13 Phase3 Info V1 NULL NULL NULL
2 14 Phase3 Done Null V2 V2 V3
2 15 Phase4 Info V1 NULL NULL NULL
2 16 Phase4 Done Null V2 V2 V3
..................
..................
etc..

Output like

Job | Phase1 Phase2 Phase3 Phase4.....
-------------------------------------------------------
V1 V2 V3 V4 V1 V2 V3 V4 V1 V2 V3 V4 V1 V2 V3 V4
1
2
...
...
--> Each Phase contains value1(V1),value2(V2),value3(V3),value4(V4)
My output is above i.e.
Each Phase contains two categories(Phase Info and Phase Done)
where Phase Info Captures value1 (V1) and PhaseDone captures
value2(V2),value3(V3),value4(V4).
But in output I have to show under Phase1-->V1,V2,V3,V4.
please help me..
sorry if not clearly explained.
Thanks in advance.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-08 : 08:57:17

EDIT: To get the output you are looking for you need to do 1 of 2 things

A) Create a PIVOT
B) If there are ONLY and ALWAYS 4 phases skip down to example B below. You will need to fill in rows for phase 3 and 4.

Assuming you want it rolled up by phase and that there can be only 1 instance of a 'Done' and 'Info' this will work.

DROP TABLE #myTable
Create table #myTable (job int, ID int, Phase char(10), Description char(10), value1 int, value2 int, value3 int, value4 int)
insert #myTable
Select 1, 1, 'Phase1', 'Info', 1, NULL ,NULL , NULL Union all
Select 1, 2, 'Phase1', 'Done', Null, 2, 4, 6 Union all
Select 1, 3, 'Phase2', 'Info', 7, NULL, NULL, NULL Union all
Select 1, 4, 'Phase2', 'Done', Null, 3, 8, 1 Union all
Select 2, 9, 'Phase1', 'Info', 24, NULL, NULL, NULL Union all
Select 2, 10, 'Phase1', 'Done', Null, 2, 4, 8 Union all
Select 2, 3, 'Phase2', 'Info', 23, NULL, NULL, NULL Union all
Select 2, 4, 'Phase2', 'Done', Null, 1, 5, 1

Select
job, Phase, Max(value1) as V1, Max(value2) as V2,Max(value3) as V3,Max(value4) as V4
FROM
#myTable
Group by
job, Phase
Order by
1,2

EXAMPLE B -

Select
job,
Max(Case when Phase = 'Phase1' then value1 end) as Phase1_V1,
Max(Case when Phase = 'Phase1' then value2 end) as Phase1_V2,
Max(Case when Phase = 'Phase1' then value3 end) as Phase1_V3,
Max(Case when Phase = 'Phase1' then value4 end) as Phase1_V4,
Max(Case when Phase = 'Phase2' then value1 end) as Phase2_V1,
Max(Case when Phase = 'Phase2' then value2 end) as Phase2_V2,
Max(Case when Phase = 'Phase2' then value3 end) as Phase2_V3,
Max(Case when Phase = 'Phase2' then value4 end) as Phase2_V4
FROM
#myTable
Group by
job
Order by
1

learntsql

524 Posts

Posted - 2009-12-09 : 00:18:23

Thank you for your reply ,
The 'PhaseInfo' and 'PhaseDone' are the values under Phase Column and apart from Phase1-->Phase4 I will be having other values under Phase Column but in output i have to take Phase1-->Phase4 Values.

Each Phase Contains 4 values V1-->V4 ;Where for PhaseInfo V1 contains Value and PhaseDone V2,V3,V4 contains values.
In Output I sholud display PhaseInfo+PhaseDone AS Phase1 and under this Phase1 i have to show values from V1-->V4.
Thank you.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-09 : 01:02:02

is this u want
small modification to dp978


SELECT
Description,
ISNULL(MAX(CASE WHEN Phase = 'Phase1' THEN value1 END),0)  + ISNULL(MAX(CASE WHEN Phase = 'Phase2' THEN value1 END),0)  AS V1,
ISNULL(MAX(CASE WHEN Phase = 'Phase1' THEN value2 END),0)  + ISNULL(MAX(CASE WHEN Phase = 'Phase2' THEN value2 END),0)  AS V2,
ISNULL(MAX(CASE WHEN Phase = 'Phase1' THEN value3 END),0)  + ISNULL(MAX(CASE WHEN Phase = 'Phase2' THEN value3 END),0)  AS V3,
ISNULL(MAX(CASE WHEN Phase = 'Phase1' THEN value4 END),0)  + ISNULL(MAX(CASE WHEN Phase = 'Phase2' THEN value4 END),0)  AS V4
FROM #myTable
GROUP BY
Description

learntsql

524 Posts

Posted - 2009-12-09 : 04:59:38

thankQ and sorry bklr,
I didnt understand the logic you wrote.
Could you please explain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources