August 10, 2011 at 6:22 am
Hi All,
I need help with writing a sql query. This is how my table is structure
Name A B C d E
John 1 0 1 1 1
Pete 1 4 0 4 0
Tom 0 4 0 0 0
Dick 0 1 7 1 1
Harry 5 1 0 9 0
Jack 1 1 0 1 3
I want a query that would take Name as an input and return only the columns that have non-zero values
E.g if in the above table when I pass John as input, the result should look like
Name A C d E
John 1 1 1 1
when I pass Harry the result should be
Name A B d
Harry 5 1 9
Any help in this regard would be very helpful. The column Name is a primary key
August 10, 2011 at 7:25 am
DROP TABLE #Sample
CREATE TABLE #Sample (Name VARCHAR(10), A INT, B INT, C INT, d INT, E INT)
INSERT INTO #Sample (Name, A, B, C, d, E)
SELECT 'John', 1, 0, 1, 1, 1 UNION ALL
SELECT 'Pete', 1, 4, 0, 4, 0 UNION ALL
SELECT 'Tom', 0, 4, 0, 0, 0 UNION ALL
SELECT 'Dick', 0, 1, 7, 1, 1 UNION ALL
SELECT 'Harry', 5, 1, 0, 9, 0 UNION ALL
SELECT 'Jack', 1, 1, 0, 1, 3
DECLARE @Statement VARCHAR(MAX)
SELECT @Statement =
'SELECT Name'
+ CASE WHEN A > 0 THEN ', A' ELSE '' END
+ CASE WHEN B > 0 THEN ', B' ELSE '' END
+ CASE WHEN C > 0 THEN ', C' ELSE '' END
+ CASE WHEN d > 0 THEN ', d' ELSE '' END
+ CASE WHEN E > 0 THEN ', E' ELSE '' END
+ ' FROM #Sample WHERE Name = ''Harry'''
FROM #Sample
WHERE Name = 'Harry'
PRINT @Statement
EXEC(@Statement) -- SEE ALSO sp_executesql
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2011 at 7:46 am
There are probably many ways to improve on this, it's not really something I've ever envisioned needing to do. But, using dynamic SQL, it could be achieved like this: -
--Sample data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
[name] VARCHAR(12),[A] INT, INT,[C] INT,[D] INT,[E] INT)
INSERT INTO #temp
SELECT 'John',1,0,1,1,1
UNION ALL SELECT 'Pete',1,4,0,4,0
UNION ALL SELECT 'Tom',0,4,0,0,0
UNION ALL SELECT 'Dick',0,1,7,1,1
UNION ALL SELECT 'Harry',5,1,0,9,0
UNION ALL SELECT 'Jack',1,1,0,1,3
UNION ALL SELECT 'Nobody',0,0,0,0,0
--####Actual Query####
--Your input
DECLARE @input VARCHAR(12)
SET @input = 'John'
--Build SQL
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'SELECT [name], '
+ COALESCE(NULLIF(CASE WHEN [A] <> 0 THEN '[A]' ELSE '' END,'') + ', ','')
+ COALESCE(NULLIF(CASE WHEN <> 0 THEN '' ELSE '' END,'') + ', ','')
+ COALESCE(NULLIF(CASE WHEN [C] <> 0 THEN '[C]' ELSE '' END,'') + ', ','')
+ COALESCE(NULLIF(CASE WHEN [D] <> 0 THEN '[D]' ELSE '' END,'') + ', ','')
+ COALESCE(NULLIF(CASE WHEN [E] <> 0 THEN '[E]' ELSE '' END,'') + ', ','')
FROM #temp
WHERE [name]=@input
--Remove any extra commas and finalise SQL statement
SET @SQL = STUFF(@SQL, LEN(@SQL), 1, '') + ' FROM #temp WHERE [name]='''+@input+''''
--Display SQL statement to be executed
PRINT @SQL
--Execute SQL statement
EXEC(@SQL)
--Edit
Beaten to the punch! Teach me for leaving a window open while doing work, then returning to it without refreshing! 😉
August 10, 2011 at 8:12 am
skcadavre (8/10/2011)
...Beaten to the punch! Teach me for leaving a window open while doing work, then returning to it without refreshing! 😉
Makes a change from lightning-fast Remi 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2011 at 8:14 am
ChrisM@Work (8/10/2011)
skcadavre (8/10/2011)
...Beaten to the punch! Teach me for leaving a window open while doing work, then returning to it without refreshing! 😉
Makes a change from lightning-fast Remi 😉
Yes it does, he managed two posts in another thread while I wrote about 8 lines. . . 😀
August 10, 2011 at 11:48 am
I knew from start that you would get JC response on this one:-D
Actually, I wanted to post similar reply, just in shorter words...
It's quite unusual to ask SQL to return same query results with non-set and variable number of columns.
Definitely not from within some client application...
HOWEVER!
Yes, there are cases when you would want to do this.
Example? Some specific data extracts! Why not?
The first row of such feed would contain column specifications and the rest would be data, so consuming system would read column specs then manipulate data as appropriate. Let say if yo have a lot of data and a lot of columns to extract, not extracting columns containing not-needed values will save some space 🙂
And yes, you can use dynamic sql to achieve it.
Personally, I wouldn't do it in T-SQL, but IT IS POSSIBLE if REQUIRED.
HOWEVER!
I might be wrong, but your example doen't look like data extract case...
August 10, 2011 at 12:00 pm
Sounds like a homework problem. There is no way that this is a business problem.
I do not see how this scenario had any practcal application.
Consider structuring your data data correctly so that you do not have to go through this.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 10, 2011 at 10:37 pm
Hi All,
Apologies if my post was rude or if it did not adhere to the standards on the forums. I had no intentions of being rude, I was just not aware of the rules. Will go through the FAQ's as suggested.
I have a table with 433 columns, each is a parameter on which the user is rated. All parameters are not applicable to all users.like in the sample table i posted, parameter B is not applicable to John and hence he gets a 0. Now i need to generate a report and send it to the user . Since there are 433 columns, it would be very confusing for the user to understand the report. So i wanted to minimize the number of columns and hence i needed the query.
Apologies once again for not adhering to the forum standards. Thank you very much for your swift responses.
Regards
Hussain
August 11, 2011 at 3:08 am
hussain27syed (8/10/2011)
I have a table with 433 columns, each is a parameter on which the user is rated. All parameters are not applicable to all users.like in the sample table i posted, parameter B is not applicable to John and hence he gets a 0. Now i need to generate a report and send it to the user . Since there are 433 columns, it would be very confusing for the user to understand the report. So i wanted to minimize the number of columns and hence i needed the query.
Your report is based on one table with 433 columns?:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply