SQL Server : combine two rows into one

Question

I want to write a query which will display the following result

FROM

  ID      Contract#          Market
   1       123kjs             40010
   1       123kjs             40011
   2       121kjs             40098
   2       121kjs             40099

TO

  ID      Contract#          Market
   1       123kjs             40010,40011
   2       121kjs             40098,40099

I been trying if statement on excel.. Thought it would be easier to write sql query. — MSTR Prime, Feb 28 '15 at 00:26
This might help: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — sgeddes, Feb 28 '15 at 00:32
Can you please **explain** what you're trying to achieve? Just dumping two piles of data on us and letting us figure it out (or *guessing it*) is not really a good strategy.... — marc_s, Feb 28 '15 at 08:14
Hey Marc_c wht im trying to achieve looks pretty self explanatory. — MSTR Prime, Feb 28 '15 at 16:07

score 0 · Answer 1 · answered Feb 28 '15 at 00:49

Try out this query, I use GROUP_CONCAT to turn column fields into 1 row field.

Also notice that you should rename the FROM clause with the name of your table.

 SELECT ID,Contract#, GROUP_CONCAT(Market SEPARATOR ',') 
    FROM nameOfThatTable GROUP BY ID;

score 0 · Answer 2 · answered Feb 28 '15 at 00:58

Try this out. I used PIVOT to solve it.

  SELECT 
         ID,
         Contract#,
         ISNULL(CONVERT(varchar,[40010]) + ',' + CONVERT(varchar,[40011]),
         CONVERT(varchar,[40098]) + ',' + CONVERT(varchar,[40099])) AS Market FROM
  ( SELECT * FROM ContractTable) AS A
  PIVOT(MIN(Market) FOR Market IN ([40010],[40011],[40098],[40099])) AS PVT
  ORDER BY ID

score 0 · Answer 3 · edited Jan 18 '21 at 12:30

0

You can use ', ' + CAST(Market AS VARCHAR(30)) in sub-query and join Id and Contract# of sub-query with outer query to get values of Market as Comma Separated Values for each Id and Contract#.

SELECT DISTINCT ID,Contract#,
SUBSTRING(
         (SELECT  ', ' + CAST(Market AS VARCHAR(30))
         FROM #TEMP T1
         WHERE T2.Id=T1.Id AND T2.Contract#=T1.Contract#             
         FOR XML PATH('')),2,200000) Market
FROM #TEMP T2

Click here to view result

Note
.........
If you want to get CSV values for Id only, remove T2.Contract#=T1.Contract# from sub-query.

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 28 '15 at 16:07

Sarath Subramanian

20,027
11
82
86

Did u get the output? Please reply @Bibek Shrestha – Sarath Subramanian Feb 28 '15 at 17:04
1

I tend to use `STUFF(, 1, 2, '')` instead of `SUBSTRING()` as it avoids a bug when the expression gets larger than the arbitrarily chosen large number. But this is the idiomatic way. – Ben Thul Feb 28 '15 at 17:06

SQL Server : combine two rows into one

3 Answers3