PIVOT not performing as expected.

Question

Sorry for an unclear question previously; hopefully I can start again...

I have this data:

entityid    name                 stringvalue
----------- -------------------- --------------------
1           ShortDescription     Coal
1           LongDescription      BlackCoal
1           ShortDescription     Gold
1           LongDescription      WhiteGold
1           ShortDescription     Steel
1           LongDescription      StainlessSteel

And this query:

select *
from
(
    select entityid, name, stringvalue as stringvalue
    from mytable
) as d
pivot
(
    min([stringvalue])
    for [name] in ([ShortDescription],[LongDescription])
)
as p

Producing this output:

entityid ShortDescription LongDescription
-------- ---------------- ---------------
1        Coal             BlackCoal

Could someone tell me why the other rows are not being produced, please? I was expecting to see:

entityid ShortDescription LongDescription
-------- ---------------- ---------------
1        Coal             BlackCoal
1        Gold             WhiteGold
1        Steel            StainlessSteel

Thanks,

Matt

Answer 1

The answer turned out to be this:

select *
from
(
    select entityid, [name], stringvalue as stringvalue
    from mytable
) as d
pivot
(
    min(stringvalue)
    for [name] in ([ShortDescription],[LongDescription])
)
as p

:)

The flaw was that the input table should have had 1, 1, 2, 2, 3, 3 for the entityid rows, respectively.

M