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_No Rank2 sysno Rank1
10844 2 18178 1
10844 4 27529 2
10845 1 22994
10845 3 22129
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_No Act1 Act2 Act3
10844 18178 27529
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_No Act1 Act2 Act3
10845 22994 22129
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 10, 2012 at 9:02 am
Apologies for not being clear .. it has been re-written..
Source Data Structure:
TABLE_A:
ID Rank2 sysno Rank1
10844 2 18178 1
10844 4 27529 2
10845 1 22994
10845 3 22129
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:
ID Actual1 Actual2 Actual3
10844 18178 27529 -
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:
ID Actual1 Actual2 Actual3
10845 22994 - 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 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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