Concatenating Column Names - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Concatenating Column Names

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2008-02-06 : 11:31:51

Hi,

I have a table that looks like this:

ID Col1 Col2 Col3 Col4
--------------------------------
1 True False True True
2 False False False True
3 False True True False

What's the most painless way of achieving the below result (concatenating the column names where the field = 'True') ?

ID Col
-----------------------------------
1 Col1, Col3, Col4
2 Col4
3 Col2, Col3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:28:52

Use this:-

SELECT t.ID,LEFT(t.Col,LEN(t.Col)-1) AS Col
FROM
(
SELECT ID,
       LTRIM(RTRIM(CASE WHEN Col1='True' THEN 'Col1,' ELSE '' END + 
       CASE WHEN Col2='True' THEN 'Col2,' ELSE '' END +
       CASE WHEN Col3='True' THEN 'Col3,' ELSE '' END +
       CASE WHEN Col4='True' THEN 'Col4,' ELSE '' END)) AS Col
FROM Table )t

singularity
Posting Yak Master

153 Posts

Posted - 2008-02-06 : 13:46:27

Visakh,

Thanks for the quick reply. Unfortunately, my situation is that I have ~100 columns across multiple tables that I have to do this for. So, I wasn't too excited about manually writing out CASE statements for all of them. Was hoping there would be an easier way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:49:18

You might have to go for dynamic SQL i guess

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 00:45:09

[code]CREATE TABLE data
(
ID int,
Col1 varchar(5),
Col2 varchar(5),
Col3 varchar(5),
Col4 varchar(5),
)

INSERT INTO data
SELECT 1, 'True', 'False', 'True', 'True' UNION ALL
SELECT 2, 'False', 'False', 'False', 'True' UNION ALL
SELECT 3, 'False', 'True', 'True', 'False'
go

CREATE FUNCTION f_concat(@ID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @cols varchar(8000)

SELECT @cols = NULL
SELECT @cols = ISNULL(@cols + ',', '') + col
FROM
(
SELECT [ID], col = 'Col1', val = Col1
FROM data
UNION ALL
SELECT [ID], col = 'Col2', val = Col2
FROM data
UNION ALL
SELECT [ID], col = 'Col3', val = Col3
FROM data
UNION ALL
SELECT [ID], col = 'Col4', val = Col4
FROM data
) c
WHERE [ID] = @ID
AND val = 'True'
ORDER BY col

RETURN @cols
END
go

SELECT [ID], [COL] = dbo.f_concat([ID])
FROM data
GROUP BY [ID]

go
DROP FUNCTION f_concat
DROP TABLE data

/*
ID COL
----------- --------------
1 Col1,Col3,Col4
2 Col4
3 Col2,Col3

(3 row(s) affected)
*/[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 04:44:25

quote:
Originally posted by singularity

Hi,

I have a table that looks like this:

ID Col1 Col2 Col3 Col4
--------------------------------
1 True False True True
2 False False False True
3 False True True False

What's the most painless way of achieving the below result (concatenating the column names where the field = 'True') ?

ID Col
-----------------------------------
1 Col1, Col3, Col4
2 Col4
3 Col2, Col3

Why do you need this?

Madhivanan

Failing to plan is Planning to fail

singularity
Posting Yak Master

153 Posts

Posted - 2008-02-11 : 09:03:53

quote:
Why do you need this?

Each column corresponds to a multi-select value from a web application. For usability purposes, the users requested that the results be displayed in a comma-separated format in a single column, as opposed to having a column for each multi-select option.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:25:36

Learn to separate application logic and database storage with business rules for displaying things.
These are sometimes NOT the same.

Store data normalized and when an user request to see the "commadelimited lsit of answers", give him just that.
Otherwise you will complicate things at the database end.

http://www.datamodel.org/NormalizationRules.html

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources