How to Query (T-SQL) one column horizontal?

Question

i have this table:

select A.field1, a.field2, a.field3, a.store
from (
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's1' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's2' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's3' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's4' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's4' as store
) A

The output is the next:

field1  field2  field3  store
g1      g2      g3      s1
g1      g2      g3      s2
g1      g2      g3      s3
g1      g2      g3      s4
g1      g2      g3      s4

And i would like the next:

field1  field2  field3  store
g1      g2      g3      s1_s2_s3_s4

Is this possible?

Thanks!

This code is really inconvenient. Are you really inserting rows by hand? Isn't there an existent table? Doesn't A exist already? — George Menoutis, Jun 25 '18 at 13:35
My customer does not want to upgrade to 2017... and i need it for a stored procedure. — Giuseppe Lolli, Jun 25 '18 at 13:40
Then you could have a field day with one of the answers to [this question](https://stackoverflow.com/q/273238/4137916). — Jeroen Mostert, Jun 25 '18 at 13:44

George Menoutis · Accepted Answer · 2018-06-25T13:41:33.047

Here you go:

;with A as(
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's1' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's2' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's3' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's4' as store union all
select 'g1' as field1, 'g2' as field2, 'g3' as field3, 's4' as store
)
select * from 
(select distinct field1,field2,field3 from A) Af
cross apply(select
                STUFF(  ( 
                        select distinct '_' + A.store
                        from ( select * from A) A
                        where Af.field1=A.field1 and Af.field2=A.field2 and Af.field3=A.field3 
                        FOR XML PATH(''), TYPE
                    ).value('.','nvarchar(max)')
                ,1,1, '') as conc_store
            ) as q1

Af is all possible values of fields, and A gets the store values concatenated.

Please note that you won't need the with A as.... part, if A actually exists as a table.

Great! I just put it into an update. Thanks! Really good query! — Giuseppe Lolli, Jun 25 '18 at 14:05

How to Query (T-SQL) one column horizontal?

1 Answers1