April 10, 2012 at 7:28 am
HI All ..
I would be grateful if you could help me out with the SQL.
I want to 'pivot' the data by having the one line for each x_No value. Also, new columns have to be generated and values assigned to them (from the sysno column) according to the ranking. If there is no values in column rank1 then rank2 has to be used.
Example Data Structure:
x_NoRank2sysnoRank1
108442181781
108444275292
10845122994
10845322129
For example for the rows = 10844
1. When Rank1 IS NOT NULL
IF Rank1 = 1 then generate column Act1 = sysno
IF Rank1 = 2 then generate column Act2 = sysno
IF Rank1 = 3 then generate column Act3 = sysno
Final Output:
x_NoAct1Act2Act3
108441817827529
2. When Rank 1 is null then use the values from Rank2 to generate the neww columns:
rows = 10845
IF Rank1 is NULL THEN
IF Rank2 = 1 then generate column Act1 = sysno
IF Rank2 = 2 then generate column Act2 = sysno
IF Rank2 = 3 then generate column Act3 = sysno
FINAL OUTPUT:
x_NoAct1Act2Act3
108452299422129
WIP SQL:
SELECT x_No,
(SELECT avg(sysno) FROM TABLE_A
WHERE colD = 1 AND x_No = 10844
GROUP BY colA) AS act1,
(SELECT avg(sysno) FROM TABLE_A
WHERE colD = 2 AND x_No = 10844
GROUP BY x_No) AS act2
FROM galaxy.pte_act_proc
GROUP BY x_No
HAVING colA = 10844
I am not sure how to use either the IF or CASE statements in this scenario.
Any help would be much appreciated.
Thanks,
April 10, 2012 at 8:02 am
Pretty hard to figure out what you are trying to do but it sounds like it may be a dynamic pivot??
Take a look at these articles from Jeff Moden.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
Also your confusion over "if" or "case":
If statements are used to control flow, case statements are used to make a conditional decision within a result set.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2012 at 8:29 am
Here's a CTE example:
with Table_A as (select 10844 x_No, 2 Rank2, 18178 sysno, 1 Rank1
union select 10844, 4, 27529, 2
union select 10845, 1, 22994, null
union select 10845, 3, 22129, null ),
Table_A_Rank AS (select *, RankMain = ISNULL(rank1,rank2) from Table_A),
Table_A_Pivot as (
select x_No, Act1 = (case RankMain when 1 then sysno end), Act2 = (case RankMain when 2 then sysno end), Act3 = (case RankMain when 3 then sysno end)
from Table_A_Rank)
select x_No, Act1 = MAX(Act1), Act2 = MAX(Act2), Act3 = MAX(Act3)
from Table_A_Pivot
group by x_No
It sets up the test data (1st CTE)
Then derives a 'master' rank column.
Then a base table which has the three Act columns.
Finally a group by to aggregate things.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 10, 2012 at 9:02 am
Apologies for not being clear .. it has been re-written..
Source Data Structure:
TABLE_A:
IDRank2sysnoRank1
108442181781
108444275292
10845122994
10845322129
1. I want to 'pivot' the data by having the one line for each ID value.
2. New columns are to be created with names Actual1, Actual2, Actual3 and values assigned to them from the sysno column accroding to their ranking:
Example:
For the two source data rows with ID=10844
Final Output:
IDActual1Actual2Actual3
108441817827529 -
For the above:
Actual1 = sysno where Rank1 = 1
Actual2 = sysno where Rank1 = 2
Actual3 = sysno where Rank1 = 3
3. However, if column Rank1 is null then the same lookup will have to be done on the column named Rank2
Example:
For the two source data rows with ID= 10845
Final Output:
IDActual1Actual2Actual3
1084522994 -22129
For the above:
Actual1 = sysno where Rank2 = 1
Actual2 = sysno where Rank2 = 2
Actual3 = sysno where Rank2 = 3
Requirement (trying to do):
I am trying to write a SQL statment which will do the above in one go.
WIP SQL:
SELECT ID,
(
SELECT
avg(sysno)
FROM
Table_A
WHERE
Rank1 = 1 AND ID = 10844
GROUP BY ID
) AS act1,
(
SELECT
avg(sysno)
FROM
Table_A
WHERE
Rank1 = 2 AND ID = 10844
GROUP BY ID
) AS act2
FROM Table_A
GROUP BY ID
HAVING ID = 10844
So far SQL OUTPUT:
ID ACTUAL1 ACTUAL2 ACTUAL3
------ --------- --------- ---------
10844 18178 27529
April 10, 2012 at 9:05 am
Hi Phil,
Many thanks! Thank you I have learned a new way of thinking and looking at things when I saw your reply. I will try and replicate your query and learn something new!
I am just wondering if this could be done with IF statements with ANSI SQL as I will at one point have to use this SQL to import data from Oracle using SSIS (sorry forr not mentioning this earlier)
April 10, 2012 at 9:09 am
aarionsql (4/10/2012)
Hi Phil,Many thanks! Thank you I have learned a new way of thinking and looking at things when I saw your reply. I will try and replicate your query and learn something new!
I am just wondering if this could be done with IF statements with ANSI SQL as I will at one point have to use this SQL to import data from Oracle using SSIS (sorry forr not mentioning this earlier)
No problem. As far as I know, Oracle supports CTEs as well, so no need to go back to the dark days of ANSI 1925 SQL 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 10, 2012 at 9:12 am
Thanks Phil .. I was googling just that! Thank you 🙂
April 10, 2012 at 10:21 am
Hi Phil .. I have got stuck in one place.
I am not being able to create an alias ('Table_A_Rank')for the following line:
Table_A_Rank AS (select *, RankMain = ISNULL(rank1,rank2) from Table_A),
Is there any way around that? It might be an Oracle thing I am guessing.
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply