Multiple value same id into single line in sql

Question

Here is my table i want to add value in one line format my code is

OrderDate OrderID ItemNo PaymentType        Cash    Nets    Visa    AMEX MasterCard Voucher Remarks ReciptNo

2018-01-27  809   40149  Mix Payment Type   NULL    NULL    NULL    NULL    NULL    55.00   NULL    NULL
2018-01-27  809   40149  Mix Payment Type   NULL    NULL    NULL    NULL    10.00   NULL    NULL    ABC123
2018-01-27  809   40149  Mix Payment Type   NULL    NULL    NULL    4.00    NULL    NULL    NULL    PQE789
2018-01-27  809   40149  Mix Payment Type   NULL    NULL    10.00   NULL    NULL    NULL    NULL    123456
2018-01-27  809   40149  Mix Payment Type   20.00   NULL    NULL    NULL    NULL    NULL    NULL    NULL

i want o/p like

OrderDate   OrderID     ItemNo       PaymentType        Cash    Nets    Visa    AMEX    MasterCard  Voucher Remarks    ReciptNo
2018-01-27  809         40149        Mix Payment Type   20.00   NULL    10.00   4.00    10.00       55.00   NULL    ABC123,PQR789,123456

what should i do for that in sql query

Are you using MySQL or MS SQL Server? – jarlh Jan 29 '18 at 12:03 — jarlh, Jan 29 '18 at 12:03

Jaydip Jadhav · Accepted Answer · 2018-01-29T12:34:10.147

3

Try something like this :

SELECT OrderDate,
       OrderID,
       ItemNo,
       PaymentType,
       MAX(Cash),
       MAX(Nets),
       MAX(Visa),
       MAX(AMEX),
       MAX(MasterCard),
       MAX(Voucher),
       MAX(Remarks),
       STUFF((
            SELECT ', ' + ISNULL(R2.ReciptNo,'')
            FROM TableName t2
            WHERE t1.OrderID= t2.OrderID
            FOR XML PATH ('')), 1, 2, '') AS ReciptNos
FROM TableName t1
GROUP BY OrderDate,OrderID,ItemNo,PaymentType

edited Jan 29 '18 at 12:34

answered Jan 29 '18 at 12:17

Jaydip Jadhav

12,179
6
24
40

1

You need to call the `.value` method on the XML you have created, otherwise you get an error saying that data type XML is not valid for the stuff function. - e.g. `FOR XML PATH(''), TYPE).value ('.', 'NVARCHAR(MAX)'), 1, 2...`. – GarethD Jan 29 '18 at 12:31
i will try let check me thank you your valuable time and answer Jaydip Jadhav sir – Tushar Jan 29 '18 at 15:28
1

@Jaydip Jadhav it's working fine thank you so much .......... – Tushar Jan 30 '18 at 10:26

score 1 · Answer 2 · answered Jan 29 '18 at 12:17

1

As you want only one column with the same values, you could go with a group by approach:

SELECT
  OrderDate, OrderID, ItemNo, PaymentType,
  MAX(cash), MAX(nets), MAX(Visa), MAX(AMEX), MAX(MasterCard), MAX(Voucher),
  STRING_AGG(Remarks, ', '), STRING_AGG(ReciptNo, ', ')
FROM myTable
GROUP BY OrderDate, OrderID, ItemNo, PaymentType

This obviously only works if you have no two rows both having for example a Cash entry.

PS: A SQLFiddle for playing around.

answered Jan 29 '18 at 12:17

contradictioned

1,253
2
14
26

`STRING_AGG` is new to SQL Server 2017 - and won't work with SQL Server 2012 (which the question is tagged with). – GarethD Jan 29 '18 at 12:28

score 0 · Answer 3 · answered Jan 29 '18 at 12:13

0

You could try

to group this on ItemNo to get single line
use MAX(Cash) and etc to get the only not null value

answered Jan 29 '18 at 12:13

Vasiliy Zverev

71
4

Multiple value same id into single line in sql

3 Answers3