February 25, 2014 at 4:50 am
Hi guys,
I have a very simple query like the following…
--------------------------------------------------------------------------------------
SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)
From this query, its returning me a result set of something like below:
--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------
RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER
Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…
Something for example (sorry, I am not trying to write a condition: my question is how to write it),
if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.
Also,
if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…I hope someone can help me into this…thanks a lot…P.S: Please see the attachment for better formatting...the post is massing up the text format...:rolleyes:
---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH
February 25, 2014 at 7:30 am
You didn't give us a whole lot here to go on. It seems that you could use a CASE expression to derive your other three columns. http://technet.microsoft.com/en-us/library/ms181765.aspx
If you need more detailed help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
February 25, 2014 at 8:07 am
Here's a sample case expression that I would start out with with a pretend join because yours was a tad confusing to me, you'll get better help by following Sean's suggestions and posting a ready made set of DDL statements. Really though, you should just check out how to write case statements in general, they're the conditional expression that you're looking for in this case.
SELECT
-- other columns can go here, omitted for this post
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'ONLINE'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'ON PRESS FOR PRINT'
END status ---<<< think of this as naming the case statement as an "on the fly" column,
--- put more case statements in as needed for each new column
FROM
-- my pretend source of tables that have the columns
table1 join table2 on table1.key = table2.key
[/CODE]
February 26, 2014 at 3:43 am
patrickmcginnis59 10839 (2/25/2014)
Here's a sample case expression that I would start out with with a pretend join because yours was a tad confusing to me, you'll get better help by following Sean's suggestions and posting a ready made set of DDL statements. Really though, you should just check out how to write case statements in general, they're the conditional expression that you're looking for in this case.
SELECT
-- other columns can go here, omitted for this post
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'ONLINE'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'ON PRESS FOR PRINT'
END status ---<<< think of this as naming the case statement as an "on the fly" column,
--- put more case statements in as needed for each new column
FROM
-- my pretend source of tables that have the columns
table1 join table2 on table1.key = table2.key
[/CODE]
Thanks mate...I have successfully solved the issue with your help...cheers...
February 26, 2014 at 6:20 am
awesome glad you got a solution!
February 27, 2014 at 1:16 am
Your query included with the three columns -
SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1,
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'ONLINE'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'ON PRESS FOR PRINT'
END AS [status],
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'WEB'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'S.R STREET, LONDON'
END AS [location],
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'BOB'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'SMITH'
END AS [contact]
FROM database_001.table2 table1
LEFT OUTER JOIN database_001.table2 table1
ON table2.column_code1 = table1.column_code1
Query with only the three expected columns as output -
SELECT CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'ONLINE'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'ON PRESS FOR PRINT'
END AS [status],
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'WEB'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'S.R STREET, LONDON'
END AS [location],
CASE WHEN column_code1 = 'RO1' AND column_description2 = 'PDF/ECOPY' THEN 'BOB'
WHEN column_code1 = 'RO5' AND column_description1 = 'JOURNAL' THEN 'SMITH'
END AS [contact]
FROM database_001.table2 table1
LEFT OUTER JOIN database_001.table2 table1
ON table2.column_code1 = table1.column_code1
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
February 27, 2014 at 1:17 am
Copy, Paste and Run....hope this helps....:-)
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
March 2, 2014 at 4:01 pm
Chandrachurh Ghosh (2/27/2014)
Copy, Paste and Run....hope this helps....:-)
Thank you so much man...really appreciate your effort... 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply