I want to pull the data from the columns where the last 2 digits are '02' as higlhlighted above.
I have tried the following query, which although it picks up the rows and columns of the desired, but also non-desired columns. Is
there a way to limit to only the highlighted.
Thanks in advance!!
SELECT Slot1, Slot2, Slot3
FROM ProcessedContainers
WHERE (SUBSTRING(Slot1, 8, 2) = '02') OR
(SUBSTRING(Slot2, 8, 2) = '02') OR
(SUBSTRING(Slot3, 8, 2) = '02')
Since you don't describe your desired output, I'm going to assume you want NULL in all the columns that don't end in '02'.
In that case, do it with CASE statements like this:
SELECT
CASE WHEN SUBSTRING(Slot1, 8, 2)='02' THEN Slot1 ELSE NULL END AS Slot1,
CASE WHEN SUBSTRING(Slot2, 8, 2)='02' THEN Slot2 ELSE NULL END AS Slot2,
CASE WHEN SUBSTRING(Slot3, 8, 2)='02' THEN Slot3 ELSE NULL END AS Slot3,
FROM ProcessedContainers
Add your WHERE clause if you want to eliminate rows that don't contain ANY '02' columns.
SELECT Slot1, null as Slot2, null as Slot3
FROM ProcessedContainers
WHERE (SUBSTRING(Slot1, 8, 2) = '02')
UNION
SELECT null, Slot2, null
FROM ProcessedContainers
WHERE (SUBSTRING(Slot2, 8, 2) = '02')
UNION
SELECT null, null, Slot3
FROM ProcessedContainers
WHERE (SUBSTRING(Slot3, 8, 2) = '02')
It's not pretty, but this will get you only the columns you want when they meet your criteria.
"What I hear, I forget; What I see, I remember; What I do, I understand." --Confucius
Remeber to Mark as Answer if this post helped you.
Marked as answer by SmokinJoe on May 22, 2012 06:18 PM
create table ProcessedContainers(id int, Slot1 varchar(10), Slot2 varchar(10),Slot3 varchar(10),Slot4 varchar(10), Slot5 varchar(10))
insert into ProcessedContainers values
(1,'0100102','0200102','0300111','04000102','05000202')
,(2,'0300001','0100202','0211120','05000221','03000102')
,(3,'0311102','0200112','0400002','07000202','03000202')
;WITH mycte AS
(SELECT id,col, val from (SELECT * FROM ProcessedContainers) src
unpivot (val for col in ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5]))unpvt
WHERE right(val,2)='02')
SELECT id, [Slot1],[Slot2],[Slot3],[Slot4],[Slot5] FROM (select id,col, val FROM mycte) src
PIVOT(max(val) FOR col IN ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5])) pvt
drop table ProcessedContainers
/*
--Result
id Slot1 Slot2 Slot3 Slot4 Slot5
1 0100102 0200102 NULL 04000102 05000202
2 NULL 0100202 NULL NULL 03000102
3 0311102 NULL 0400002 07000202 03000202
*/
Thank you for your solution, it is very elegant... and would love to use it, however I am getting the following error when attempting to run this in SQL Server Management Studio.
I made some minor changes to account for the corrected table name (HANES_ProcessedContainers), and the name of the id (PC_ID) field, as follows:
;WITH mycte AS
(SELECT PC_ID,col, val from (SELECT * FROM Hanes_ProcessedContainers) src
unpivot (val for col in ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5]))unpvt
WHERE right(val,2)='02')
SELECT PC_ID, [Slot1],[Slot2],[Slot3],[Slot4],[Slot5] FROM (select
PC_ID,col, val FROM mycte) src
PIVOT(max(val) FOR col IN ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5])) pvt
I receive the following errors:
"Unable to parse query text" Error Message: Line 1: Incorrect syntax near ';'. Line 1: Incorrect Syntax near 'unpivot'. Line 1: Incorrect Syntax near 'PIVOT'.
Your further assistance would be appreciated. Thanks!!
The code should work with your modification. Your database should be SQL Server 2005 and above with competibility level above 90, just in case you need this information.
Here is the sample with the new table and column name.
-- create table Hanes_ProcessedContainers(PC_ID int, Slot1 varchar(10), Slot2 varchar(10),Slot3 varchar(10),Slot4 varchar(10), Slot5 varchar(10))
--insert into Hanes_ProcessedContainers values
--(1,'0100102','0200102','0300111','04000102','05000202')
-- ,(2,'0300001','0100202','0211120','05000221','03000102')
-- ,(3,'0311102','0200112','0400002','07000202','03000202')
;WITH mycte AS
(SELECT PC_ID,col, val from (SELECT * FROM Hanes_ProcessedContainers) src
unpivot (val for col in ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5]))unpvt
WHERE right(val,2)='02')
SELECT PC_ID, [Slot1],[Slot2],[Slot3],[Slot4],[Slot5] FROM (select PC_ID,col, val FROM mycte) src
PIVOT(max(val) FOR col IN ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5])) pvt
--drop table Hanes_ProcessedContainers
Checked the database and the current compatibility level is SQL Server 2000(80). As I am not the owner of rh DB, I would not want to make any changes if at all possible... would you have any other suggestions...
SmokinJoe
Member
88 Points
224 Posts
SQL Column Selection Based on Meeting Criteria
May 22, 2012 06:03 PM|LINK
My table contains several rows, with the following data:
Row Columns
Slot1 Slot2 Slot3 Slot4 Slot5
1 0100102 0200102 0300111 04000102 05000202
2 0300001 0100202 0211120 05000221 03000102
3 0311102 0200112 0400002 07000202 03000202
4 ...
I want to pull the data from the columns where the last 2 digits are '02' as higlhlighted above.
I have tried the following query, which although it picks up the rows and columns of the desired, but also non-desired columns. Is
there a way to limit to only the highlighted.
Thanks in advance!!
SELECT Slot1, Slot2, Slot3
FROM ProcessedContainers
WHERE (SUBSTRING(Slot1, 8, 2) = '02') OR
(SUBSTRING(Slot2, 8, 2) = '02') OR
(SUBSTRING(Slot3, 8, 2) = '02')
TabAlleman
All-Star
15741 Points
2724 Posts
Re: SQL Column Selection Based on Meeting Criteria
May 22, 2012 06:10 PM|LINK
Since you don't describe your desired output, I'm going to assume you want NULL in all the columns that don't end in '02'.
In that case, do it with CASE statements like this:
Add your WHERE clause if you want to eliminate rows that don't contain ANY '02' columns.
grundebar
Contributor
4515 Points
726 Posts
Re: SQL Column Selection Based on Meeting Criteria
May 22, 2012 06:13 PM|LINK
You could do this with a union statement:
It's not pretty, but this will get you only the columns you want when they meet your criteria.
Remeber to Mark as Answer if this post helped you.
SmokinJoe
Member
88 Points
224 Posts
Re: SQL Column Selection Based on Meeting Criteria
May 22, 2012 06:18 PM|LINK
Pretty or not... Gets the job done!
Thanks!!
limno
All-Star
117430 Points
8032 Posts
Moderator
MVP
Re: SQL Column Selection Based on Meeting Criteria
May 23, 2012 04:41 AM|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
SmokinJoe
Member
88 Points
224 Posts
Re: SQL Column Selection Based on Meeting Criteria
May 23, 2012 12:49 PM|LINK
Hello Limno,
Thank you for your solution, it is very elegant... and would love to use it, however I am getting the following error when attempting to run this in SQL Server Management Studio.
I made some minor changes to account for the corrected table name (HANES_ProcessedContainers), and the name of the id (PC_ID) field, as follows:
;WITH mycte AS
(SELECT PC_ID,col, val from (SELECT * FROM Hanes_ProcessedContainers) src
unpivot (val for col in ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5]))unpvt
WHERE right(val,2)='02')
SELECT PC_ID, [Slot1],[Slot2],[Slot3],[Slot4],[Slot5] FROM (select PC_ID,col, val FROM mycte) src
PIVOT(max(val) FOR col IN ([Slot1],[Slot2],[Slot3],[Slot4],[Slot5])) pvt
I receive the following errors:
"Unable to parse query text"
Error Message: Line 1: Incorrect syntax near ';'.
Line 1: Incorrect Syntax near 'unpivot'.
Line 1: Incorrect Syntax near 'PIVOT'.
Your further assistance would be appreciated. Thanks!!
limno
All-Star
117430 Points
8032 Posts
Moderator
MVP
Re: SQL Column Selection Based on Meeting Criteria
May 23, 2012 01:25 PM|LINK
The code should work with your modification. Your database should be SQL Server 2005 and above with competibility level above 90, just in case you need this information.
Here is the sample with the new table and column name.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
SmokinJoe
Member
88 Points
224 Posts
Re: SQL Column Selection Based on Meeting Criteria
May 23, 2012 01:40 PM|LINK
Thank you, once again... however I still receive the same errors. Yes, am using SQL Server 2005... not sure how to check compatibility level?
Is there another way, perhaps, to do this? It kicks out at the ";WITH" statement...
Thanks!!
limno
All-Star
117430 Points
8032 Posts
Moderator
MVP
Re: SQL Column Selection Based on Meeting Criteria
May 23, 2012 01:45 PM|LINK
Check your database compatibility level:
You can run the following in SSMS to change the database compatibility level:
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
SmokinJoe
Member
88 Points
224 Posts
Re: SQL Column Selection Based on Meeting Criteria
May 23, 2012 01:57 PM|LINK
Hi,
Checked the database and the current compatibility level is SQL Server 2000(80). As I am not the owner of rh DB, I would not want to make any changes if at all possible... would you have any other suggestions...
Thanks!