CODEWITHSUNDEEP
sql-server-2008
Utkarsh Verma
Utkarsh Verma

Reputation: 27

How to concatenate two rows and sort in according to a column in SQL

comp   vol      Volume     ID       Seg    name
CSQ   9000      15000    ANSD-09-MN-02  EWW    a
CSY   11000     15000    ANSD-09-MN-02  EWW    a
CCB  0.020679   15       ANSD-09-W1-02  CID    W
CID  0.868951   15       ANSD-09-W1-02  CID    W
CSQ  9.919137   15       ANSD-09-W1-02  CID    W
CSY  4.139181   15       ANSD-09-W1-02  CID    W

I have a table like above. I want to write a query such that I have a table like:

Volume   BatchID         Seg   Name          Crucomp
15000   ANSD-09-MN-02    EWW    a         (CSQ-9000)(CSY-11000)
15000   ANSD-09-W1-02    CID    W    (CCB-0.020679)(CID-0.868951)(CSQ-9.919137  )(CSY-4.139181).

I don't have a column called crucomp and the data in the crucomp is the concatenation of volume and composition for a particular ID.

I have tried different means for achieving the desired result but I am not able to get the desired result.

Need Help on this. Thanks

Upvotes: 2

Views: 100

Answers (1)

M.Ali
M.Ali

Reputation: 69554

SQL Fiddle

MS SQL Server 2014 Schema Setup:

Query 1:

DECLARE @TABLE TABLE(comp VARCHAR(20), vol VARCHAR(10), Volume INT, 
                     ID VARCHAR(20), Seg VARCHAR(10),name VARCHAR(10))
INSERT INTO @TABLE VALUES
('CSQ' ,  '9000'   ,   15000 ,'ANSD-09-MN-02','EWW','a'),
('CSY' ,  '11000'  ,   15000 ,'ANSD-09-MN-02','EWW','a'),
('CCB' , '0.020679',   15    ,'ANSD-09-W1-02','CID','W'),
('CID' , '0.868951',   15    ,'ANSD-09-W1-02','CID','W'),
('CSQ' , '9.919137',   15    ,'ANSD-09-W1-02','CID','W'),
('CSY' , '4.139181',   15    ,'ANSD-09-W1-02','CID','W')

SELECT t.Volume
      ,t.ID AS BatchID
      ,t.Seg
      ,t.name
      ,STUFF((SELECT '(' + comp + '-'+ Vol + ')'
              FROM @TABLE  
              WHERE Volume = t.Volume AND Seg = t.Seg
               AND ID = t.ID AND name = t.name 
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,0,'') AS Crucomp
FROM @TABLE t
GROUP BY t.Volume
      ,t.ID 
      ,t.Seg
      ,t.name
ORDER BY t.Volume DESC

Results:

| Volume |       BatchID | Seg | name |                    Crucomp           
|--------|---------------|-----|------|----------------------------------------------------------|
|  15000 | ANSD-09-MN-02 | EWW |    a |       (CSQ-9000)(CSY-11000) 
|     15 | ANSD-09-W1-02 | CID |    W | (CCB-0.020679)(CID-0.868951)(CSQ-9.919137)(CSY-4.139181) |

Upvotes: 1

Related Questions