Pivot when value into columns are duplicate

Question

From the result of this query:

+--------------------------------------+-----------+--------+
|                  id                  |   name    |  data  |
+--------------------------------------+-----------+--------+
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | LMP1(FA5) | NULL   |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Limits    | NULL   |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Low       | 1      |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | High      | 3      |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Units     | second |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Comp      | GELE   |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Data      | 1.8    |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Status    | Passed |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | LMP2(FA6) | NULL   |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Limits    | NULL   |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Low       | 1      |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | High      | 3      |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Units     | second |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Comp      | GELE   |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Data      | 1.8    |
| 4EAB4160-75FF-11E8-8719-2C56DC925591 | Status    | Passed |
+--------------------------------------+-----------+--------+

I would like to get this:

I've tried this, but is not the definitive answer:

SELECT *
FROM
(
SELECT  id, name, data as f FROM temp_id)t
PIVOT(MIN(f)
      FOR name
      IN ([LMP1 (FA5)],[Limits],[Low],[High],[Units],[Comp],[data],[Status])
      )p

it would be nice to have the name of the dynamic columns also...

I thank everyone for the help.

I've tried this dynamic pivot, but it doesn't work:

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

 SET @cols = STUFF((SELECT ',' + QUOTENAME(c.name) 
            FROM temp_id c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' from 
            (
                select id
                    , name
                    , data
                from temp_id
           ) x
            pivot 
            (
                count(name)
                for name in (' + @cols + ')
            ) p

sounds like dynamic sql is going to be your best bet here. i am going to be tied up most of the day and i wont be able to put together a formal answer until late but here is a starting point. This is a link to a dynamic sql pivot https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query once you have it pivoted out you can combine the LMP1 fields with another dynamic sql query. so the flow would be pivot -> identify -> merge columns -> output. — SFrejofsky, Jul 05 '18 at 13:42
thanks SFrejofsky. I tried with a dynamic pivot, but I always get errors because the fields are not distinct in the pivot clausole, not work... — harmrx, Jul 05 '18 at 14:45
How do you know that value `1.8` from `data` applies to `LMP1 (FA5)`? There is no link between your wanted rows (LMP) that you can use to pivot the other columns. You will need, at least, a column to order by so you can generate an ID to group them together. You won't be able to pivot correctly without this. — EzLo, Jul 06 '18 at 08:37
Thanks for you reply EzLo, on a non-dynamic pivot: SELECT * FROM ( SELECT id, name, data as f FROM temp_id)t PIVOT(MIN(f) FOR name IN ([LMP1 (FA5)],[Limits],[Low],[High],[Units],[Comp],[data],[Status]) )p I get this result: id LMP1 (FA5) Limits Low High Units Comp data Status 4EAB4160-75FF-11E8-8719-2C56DC925591 NULL NULL 1 3 second GELE 1.8 Passed but I was looking for a way to get LMP1 (FA) 5 and LMP2 (FA6) for each line. is it possible to generate what you say during an intermediate step? — harmrx, Jul 06 '18 at 08:53

Pivot when value into columns are duplicate

0 Answers0