Change structure of result in SQL Server

Question

I have to convert this:

id name   product description
---------------------------
1  Kurt   p1      water
1  Kurt   p2      salt
2  Claude p3      pepper
2  Claude p4      mint

into this:

id name   product1 description1 product2 description2
-----------------------------------------------------
1  Kurt   p1       water        p2       salt
2  Claude p3       pepper       p4       mint

please, I was searching for an answer and I didn't find this...

Table structure is like:

client (id, name, and more data)
products (id, description, and more data)
prodclient (id, idclient, idproduct)

Thank you

PD. the rows I want to add into the 2nd result columns, are the same data, not the addition of values.

possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Apr 10 '15 at 18:33
no, there are more than 2 products, but I want to show only 2 discarding the 3rd and the others... and, I wantto show up the secondrecord, no show a total. u.u — Kurt Reload, Apr 10 '15 at 18:54

score 1 · Accepted Answer · answered Apr 10 '15 at 18:57

Try this

http://sqlfiddle.com/#!6/12b1c/6/0

CREATE TABLE Products
    ( id   int, name varchar(6), product varchar(2), description varchar(6))
;

INSERT INTO Products
    (id, name, product, description)
VALUES
    (1, 'Kurt', 'p1', 'water'),
    (1, 'Kurt', 'p2', 'salt'),
    (2, 'Claude', 'p3', 'pepper'),
    (2, 'Claude', 'p4', 'mint')
;

WITH ProductList as
(
select 
name,
max(product) as product1,
min (product) as product2

from Products

group by name
  )

 select pl.*,p1.description as description1 , p2.description as description2
 from ProductList pl
Left Outer Join  Products P1
on pl.product1 = p1.product
Left Outer Join  Products P2
on pl.product2 = p2.product

Change structure of result in SQL Server

1 Answers1