Column Names which have a value per row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column Names which have a value per row

Author

Topic

robbydogg
Starting Member

6 Posts

Posted - 2011-10-18 : 08:18:42

Hello,

I have tried to set up a query that looks at a table and returns the column name for when there is data within that column.
for example -

Part Number| Fits Car | Fits Van | Fits Caravan |

Part1 | y | n | n |
Part2 | n | y | y |
Part3 | n | n | y |

and the query bring up:
Part1 Fits Car
Part2 Fits Van
Part2 Fits Caravan
Part3 Fits Caravan

and so on - is this possible without hard-coding all the column names?

thanks

For each problem, there is a solution. For each solution there is someone with another problem to render the original solution useless

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-18 : 08:26:44

only one of column with value y ?


select PartNumber,
       case when FitsCar = 'y' then 'Fits Car'
            when FitsVan = 'y' then 'Fits Van'
            when Caravan = 'y' then 'Fits Caravan'
            end
from   yourtable

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 08:45:58


;With CTE
AS
(
SELECT [part number],
Category,
Val
FROM table t
UNPIVOT(Val FOR Category IN ([Fits Car],[Fits Van],[Fits Caravan]))u
)

SELECT c.[part number],
STUFF((SELECT ',' + Category FROM CTE WHERE [part number]=c.[part number] AND Val='y' FOR XML PATH('')),1,1,'') AS [List]
FROM CTE c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robbydogg
Starting Member

6 Posts

Posted - 2011-10-18 : 08:58:59

quote:

Originally posted by khtan

only one of column with value y ?


select PartNumber,
       case when FitsCar = 'y' then 'Fits Car'
            when FitsVan = 'y' then 'Fits Van'
            when Caravan = 'y' then 'Fits Caravan'
            end
from   yourtable

KH
[spoiler]Time is always against us[/spoiler]

Hi,
close, but it still requires me to put in the column names - what i was hoping for is one that works this out for me - basicallin in columns 31 onwards the value in their records would be either 'Y' or 'NULL' and i only want the 'Y' columns per part number.

Thanks tho - it's got me started

For each problem, there is a solution. For each solution there is someone with another problem to render the original solution useless

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-18 : 09:08:26

you will need to use INFORMATION_SCHEMA.COLUMNS with Dynamic SQL
see http://www.sommarskog.se/dynamic_sql.html on Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

robbydogg
Starting Member

6 Posts

Posted - 2011-10-18 : 09:33:39

quote:
Originally posted by visakh16

;With CTE
AS
(
SELECT [part number],
Category,
Val
FROM table t
UNPIVOT(Val FOR Category IN ([Fits Car],[Fits Van],[Fits Caravan]))u
)

SELECT c.[part number],
STUFF((SELECT ',' + Category FROM CTE WHERE [part number]=c.[part number] AND Val='y' FOR XML PATH('')),1,1,'') AS [List]
FROM CTE c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi,
Thanks also, this is close as well, but still needs the column names.

I have a piece of code which displays the columns needed, but i'm not sure how to relate these with their part numbers which have a corresponding 'Y'

SELECT *
--column_name
FROM information_schema.columns
WHERE table_name = 'TABLE_CONTAINING_DATA' and ORDINAL_POSITION > 31

For each problem, there is a solution. For each solution there is someone with another problem to render the original solution useless

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 09:48:38

make it dynamic then

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources