CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
user786
user786

Reputation: 4364

merging columns data into string -- sql server

I have following data in one table

enter image description here

I tried a lot but remain unsucessful,

What I want to achieve is following

enter image description here

UPDATE THERE CAN BE ANY NUMBER OF ATTCODE, MEANS I DONT KNOW HOW MANY ATTCODE CAN BE THERE

any help will be appreciated

Upvotes: 1

Views: 62

Answers (2)

gofr1
gofr1

Reputation: 15987

That will work for any number of ATTCodes:

;WITH cte AS (
SELECT *
FROM (VALUES
(1, 'CL', 10),
(1, 'SL', 20),
(2, 'CL', 9),
(2, 'SL', 2)) as t(EmpId, ATTCode, [Count])
)

select DISTINCT  c.EmpId,
        REPLACE(STUFF((SELECT ',' + ATTCode +':'+CAST([Count] as nvarchar(5)) 
        FROM cte s
        WHERE s.EmpId = c.EmpId
        FOR XML PATH('')),1,1,''),',',' & ') as [Str]
from cte c

Result:

EmpId       Str
----------- ----------------
1           CL:10 & SL:20
2           CL:9 & SL:2

(2 row(s) affected)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Although aggregate string concatenation is the most general method, if you only have two values per empid, you could do:

select empid,
       (min(attcode + ':' + cast(count as varchar(255)) + ' & ' +
        max(attcode + ':' + cast(count as varchar(255))
       ) as str
from t
group by empid;

This is not a general solution but it might solve your problem if you have example two values for each group.

Upvotes: 3

Related Questions