September 9, 2010 at 1:25 am
have one table with following columns
id name age address salary
112 abc$100
now i am using a query
Select
Case when id='' then 'id,' Else '' End +
Case when name= '' then 'name,' Else '' End +
Case when age= '' then 'age,' Else '' End +
case when address= ''then 'Sum_Insured' else '' End +
Case when salary= '' then 'salary,' Else '' End as nullcolumn from table
this query fetch all the column which are bank for a perticular id as nullcolumn
now i want my resultset to be displayed according to id's, like:
id nullcolumn
1 name
1 age
1 address
1 ..... and so on
means the above query result ie. nullcolumn values to be displayed rowwise with respect to the id.
September 9, 2010 at 1:59 am
ORDER BY ID
??
September 9, 2010 at 2:28 am
Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 2:13 pm
For a reasonable number of columns and/or on a reasonably sized table, this should work OK.
For a table with a very large number of columns or a large number of rows, you would likely be better off using your original method and just splitting the final results.
SELECT id,
CASE col#
WHEN 1 THEN 'id'
WHEN 2 THEN 'name'
WHEN 3 THEN 'age'
WHEN 4 THEN 'address'
WHEN 5 THEN 'salary' END AS NullColumn
FROM tablename
CROSS JOIN (
SELECT 1 AS col# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5
) AS col#s
WHERE CASE col#
WHEN 1 THEN id
WHEN 2 THEN name
WHEN 3 THEN age
WHEN 4 THEN address
WHEN 5 THEN salary END = ''
ORDER BY id, col#
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply