How do I pivot this in T-SQL?

Question

How do I get this:

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

To become this:

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

The code I have so far is:

    select *
from (select entityid, cast(name as nvarchar(20)) as name, cast(stringvalue as nvarchar(20)) as stringvalue from metapropertyvalue) as d
pivot
(
    max(stringvalue)
    for [name] in ([ShortDescription],[LongDescription])
)
as p

Many thanks everyone,

Matt.

Answer 1

Leaving the layout of your table aside, in order to do what you want you shouldn't be using PIVOT you'll need subqueries for that.

Answer 2

The pivot keyword is for aggregation use pretty much exclusively. You'll likely have to do this manually via subqueries.

That aside, your table layout is a really bad idea. I've done this before, I know lots of other people that have done this before, it does NOT scale well. The biggest problem is that you can't properly define indices on your data. You should seriously consider just changing the table directly to your "pivoted" format as that's a proper relational style for it anyways.

Answer 3

Assuming that your DBRM can't be changed, this is the closest I can get after a several workarounds:

select TOP 1 Id
    , LEFT(ShortDescription, LEN(ShortDescription) - 1) as ShortDescription
    , LEFT(LongDescription, LEN(LongDescription) - 1) as LongDescription
from (
    select Entity_Id as Id
            , (
                select StringValue + N', ' as [text()]
                    from MyEntities
                    where [Name] LIKE N'ShortDescription'
                    FOR XML PATH(N'')
            ) as ShortDescription
            , (
                select StringValue + N', ' as [text()]
                    from MyEntities
                    where [Name] LIKE N'LongDescription'
                    FOR XML PATH(N'')
            ) as LongDescription
        from MyEntities
) e

Which will result in output:

Id | ShortDescription | LongDescription

1 | Coal, Gold, Steel | BlackCoal, WhiteGold, StainlessSteel

And I doubt this is functional, though I don't know your situation.

Only consider doing so if you manage to format the data before display to the GUI or something like that.

By the way, the PIVOT stuff and such aggregation will only work when the data consists of numbers. There would have been other ways to achieve this desired result if your [Name] and StringValue columns would have been of numerics.

On the other hand, we're facing a design smell here.

Instead of designing a table like you did and always have to "pivot" it and do some complex code to retrive the information from it, in addition to always mention in the [Name] column whether it is a ShortDescription or a LongDescription, I would recommend the following to design the table the way you want data for output, as it is the normality, if I may say so.

IF OBJECT_ID(N'MyEntitiesTable') IS NOT NULL
    DROP TABLE MyEntitiesTable
GO

CREATE TABLE MyEntitiesTable (
    EntityId int IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ShortDescription nvarchar(10) NOT NULL
    LongDescription nvarchar(50) NOT NULL
)
GO

insert into MyEntities (ShortDescription, LongDescription) values (N'Coal', N'BlackCoal')
GO
insert into MyEntities (ShortDescription, LongDescription) values (N'Gold', N'WhiteGold')
GO
insert into MyEntities (ShortDescription, LongDescription) values (N'Steel', N'WhiteSteel')
GO

This way, all you will be required to write as a query is the following:

select EntityId
        , ShortDescription
        , LongDescription
    from MyEntitiesTable

As for the EntityId field, if you absolutely want it to be always the number 1, then you can omit the IDENTITIY(1, 1) PRIMARY KEY thing within the table creation. However, I strongly recommend you let it there as this defines your primary key, and no table within your model should have no primary key.

The reason for this prefered approach is that for each description you have to add to your data table, you will have to perform two INSERTs statements. This is overkilling the transactions against your database, plus, making it very hard to exploit, as you can see with your current situation.