CODEWITHSUNDEEP
sqlsql-serverpivot
Rasmus Edvardsen
Rasmus Edvardsen

Reputation: 169

get 2 column values to 2 columns for 1 row (or comma-separated)

I have a problem where I have to alter another programmer's very large SQL query. My problem is described by this table:

+--------+-----------+---------------+-------------------------+
|   Id   | ProductId |    Barcode    |        CreatedAt        |
+--------+-----------+---------------+-------------------------+
|  30665 |    312118 | 4054065383840 | 2017-03-13 18:37:13.130 |
| 128600 |    312118 | 4054065383857 | 2017-05-22 13:26:48.683 |
+--------+-----------+---------------+-------------------------+

So as you can see, a product has 2 barcodes. In our query, I need to display these 2 barcodes in 2 columns, instead of 2 different rows, as barcode1 and barcode2, or some such.

  SELECT ISNULL(pp.Barcode,'') AS BarCode
  FROM (...) c
  LEFT JOIN ProductBarcode pp on pp.ProductId=c.VariantProductId

This is the query in use atm. Hopefully I explained it well enough. Thanks in advance, Rasmus.

EDIT: db is MSSQL

Upvotes: 1

Views: 436

Answers (1)

Ven
Ven

Reputation: 2014

Use pivot query , much effective in this scenario. Use more row numbers if there are more than 2 barcodes as barcode1,barcode2,barcode3 

SELECT productid
    ,[1] AS Barcode1
    ,[2] AS Barcode2
FROM (
    SELECT productid
        ,barcode
        ,ROW_NUMBER() OVER (
            PARTITION BY productid ORDER BY barcode
            ) rn
    FROM #mytable
    ) my
pivot(max(barcode) FOR rn IN ([1], [2])) AS pvt

Upvotes: 1

Related Questions