September 1, 2015 at 2:34 pm
DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))
INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL
SELECT 3, 'G', 'H', 'I'
UNION ALL
SELECT 4, 'J', 'K', 'L'
Select * FROm @Tab1
DECLARE @TAB2 TABLE (ID INT , SV varchar(10), Col1 varchar(10), Col2 Varchar(10))
INSERT INTO @Tab2
SELECT 1, 'C', 'XYZ', '123'
UNION ALL
SELECT 2, 'D', 'AAA', '111'
UNION ALL
SELECT 3, 'J', 'BBB', '222'
Select * From @Tab2
Need query to get the Result as below
PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL
By using case statement we can retrieve the data, but looking for alternate way to do it.
September 1, 2015 at 2:50 pm
Thugs (9/1/2015)
DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL
SELECT 3, 'G', 'H', 'I'
UNION ALL
SELECT 4, 'J', 'K', 'L'
Select * FROm @Tab1
DECLARE @TAB2 TABLE (ID INT , SV varchar(10), Col1 varchar(10), Col2 Varchar(10))
INSERT INTO @Tab2
SELECT 1, 'C', 'XYZ', '123'
UNION ALL
SELECT 2, 'D', 'AAA', '111'
UNION ALL
SELECT 3, 'J', 'BBB', '222'
Select * From @Tab2
Need query to get the Result as below
PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL
By using case statement we can retrieve the data, but looking for alternate way to do it.
This looks like a pretty typical cross tab to me. Using a case expression is a great way to do this. Is there a reason you don't like what you have already done?
_______________________________________________________________
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/
September 1, 2015 at 2:52 pm
Just Wanted to check if there is any better way to write the query without case statements.
September 1, 2015 at 2:57 pm
Thugs (9/1/2015)
Just Wanted to check if there is any better way to write the query without case statements.
Well...we haven't actually seen the query so I can't say there is no room for improvement.
_______________________________________________________________
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/
September 1, 2015 at 3:43 pm
Thanks Sean.. here is the query I have written.
DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))
INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL
SELECT 3, 'G', 'H', 'I'
UNION ALL
SELECT 4, 'J', 'K', 'L'
Select * FROm @Tab1
DECLARE @TAB2 TABLE (ID INT , SV varchar(10), Col1 varchar(10), Col2 Varchar(10))
INSERT INTO @Tab2
SELECT 1, 'C', 'XYZ', '123'
UNION ALL
SELECT 2, 'D', 'AAA', '111'
UNION ALL
SELECT 3, 'J', 'BBB', '222'
Select * From @Tab2
Select A.PS,
CASE WHEN A.PS = B.SV THEN B.Col1 END AS PS_COl1,
CASE WHEN A.PS = B.SV THEN B.Col2 END AS PS_COl2, A.NPS1,
CASE WHEN A.NPS1 = B.SV THEN B.Col1 END AS NPS1_COl1,
CASE WHEN A.NPS1 = B.SV THEN B.Col2 END AS NPS1_COl2,A.NPS2,
CASE WHEN A.NPS2 = B.SV THEN B.Col1 END AS NPS2_COl1,
CASE WHEN A.NPS2 = B.SV THEN B.Col2 END AS NPS2_COl2
FROM @Tab1 A
INNER JOIN @Tab2 B ON A.PS = B.SV OR A.NPS1 = B.SV OR A.NPS2 = B.SV
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply