CODEWITHSUNDEEP
sql-server-2008
Gary
Gary

Reputation: 27

How to display column values horizontally (in a row) with some text around it?

If I have a column "Col1" with values 1,2,3 

col1
****
1
2
3

How can I show them horizontally? like 

col1 1,2,3

Or if possible with some text like

**'values for col1 are' 1,2,3**

any suggestions will be much appreciated :)

Upvotes: 0

Views: 282

Answers (1)

iamdave
iamdave

Reputation: 12243

To answer your specific question you can achieve this with for xml and some little tricks:

declare @t table(Col1 int);
insert into @t values(1),(2),(3);

select 'Values for Col1 are '
        + stuff((select ', ' + cast(Col1 as varchar(5))
                from @t
                for xml path('')
                ),1,2,'');

This works by concatenating each row value into a very minimal xml string with only a comma and a space delimiting each value. The first comma and space are then removed using the stuff function.

In this case, the output is: Values for Col1 are 1, 2, 3

If you have a column that you would want to group your output by, you can add this into the above script thus:

declare @t table(Id int, Val int);
insert into @t values(1,1),(1,2),(1,3),(2,4),(2,2);

with Ids as
(
    select distinct Id
    from @t
)
select 'Values for ID ' + cast(Ids.Id as varchar(5)) + ' are '
        + stuff((select ', ' + cast(t.Val as varchar(5))
                from @t as t
                where Ids.Id = t.Id
                for xml path('')
                ),1,2,'') as Vals
from Ids
order by IDs.Id;

Which outputs:

+-----------------------------+
|             Vals            |
+-----------------------------+
| Values for ID 1 are 1, 2, 3 |
| Values for ID 2 are 4, 2    |
+-----------------------------+

Upvotes: 2

Related Questions