How to Use Aggregate Case Statements Instead of PIVOT When You Don't Know the Upper Bound?

Question

Solution: I wasn't able to solve this without going beyond a simple query, so I've resorted to hard-coding the case statements up to the limit of my table's numeric identifier.

I'm looking for help in writing a query to represent an unknown number of records across multiple fields with only one record per Primary Key.

Here's my table design:

[Column Name] | [Data Type]  | [Allow Nulls]
-------------------------------------------
*ItemRef        nvarchar(48)    Unchecked
*AttributeID    numeric(2, 0)   Unchecked
 AttributeName  nvarchar(128)   Unchecked
 AttributeValue nvarchar(3072)  Nullable
 AttributeUOM   nvarchar(10)    Nullable

*EDIT: Here's some sample data:

    Product123 | 1 | Brand | MyBrandName
    Product123 | 2 | Product Line | MyProductLine
    Product123 | 3 | Color | MyColor
    Product456 | 1 | Brand | MySecondBrandName
    Product456 | 2 | Style | MyStyle

Here's My Desired Query Result:

[ItemRef] | [AttributeName_01] | [AttributeValue_01] | [AttributeName_02] | [AttributeValue_02] | etc...

At first I thought to use a PIVOT query, but ran across multiple threads on here suggesting I try Aggregate Case statements instead, which works much more efficiently at first glance.

However, I don't know how many attributes a single record gets. So my question is, how can I write the following to better represent an uncertain amount of attributes?

SELECT ItemRef
  , MIN(CASE AttributeID WHEN '1' THEN AttributeName END) AS AttrName01
  , MIN(CASE AttributeID WHEN '1' THEN AttributeValue END) AS AttrValue01
  , MIN(CASE AttributeID WHEN '1' THEN AttributeUOM END) AS AttrUom01
  , MIN(CASE AttributeID WHEN '2' THEN AttributeName END) AS AttrName02
  , MIN(CASE AttributeID WHEN '2' THEN AttributeValue END) AS AttrValue02
  , MIN(CASE AttributeID WHEN '2' THEN AttributeUOM END) AS AttrUom02
  , MIN(CASE AttributeID WHEN '3' THEN AttributeName END) AS AttrName03
  , MIN(CASE AttributeID WHEN '3' THEN AttributeValue END) AS AttrValue03
  , MIN(CASE AttributeID WHEN '3' THEN AttributeUOM END) AS AttrUom03
  , MIN(CASE AttributeID WHEN '4' THEN AttributeName END) AS AttrName04
  , MIN(CASE AttributeID WHEN '4' THEN AttributeValue END) AS AttrValue04
  , MIN(CASE AttributeID WHEN '4' THEN AttributeUOM END) AS AttrUom04
. . .
  , MIN(CASE AttributeID WHEN '99' THEN AttributeName END) AS AttrName05
  , MIN(CASE AttributeID WHEN '99' THEN AttributeValue END) AS AttrValue05
  , MIN(CASE AttributeID WHEN '99' THEN AttributeUOM END) AS AttrUom05
FROM dbo.ProductAttributes
GROUP BY ItemRef

What you are referring to is known as a dynamic cross tab. Here is an article on the topic. http://www.sqlservercentral.com/articles/Crosstab/65048/ If you have problems figuring that out post back with some sample data and I can help you. — Sean Lange, Feb 09 '17 at 16:20
Okay, I've read the article you linked, and it did seem a bit over my head. Does this absolutely require creating a stored procedure? As for sample data, I'll update the main post I guess. — Ryan Cortino, Feb 09 '17 at 16:46
It certainly doesn't require a stored procedure but why are you not wanting to create one? Doing this in an ad hoc query will be a syntactical nightmare. It is difficult enough in t-sql, but trying to build dynamic sql in code is just insane and highly prone to sql injection vulnerability. — Sean Lange, Feb 09 '17 at 16:49
Well, to be frank, I'm not familiar with the issues you've raised at all. I'm an application developer and I'm in need of a view a simple view that I can use for building a dataset. I don't know what an ad-hoc query is, and I have very little experience in t-sql. I wouldn't want to be doing insane work however, so I'm open to learn. — Ryan Cortino, Feb 09 '17 at 16:53
OK no problem. You can't have dynamic sql in a view. If you can post some sample data, not real information of course, I can help. I don't need a lot of data but maybe 3-5 rows or so? An adhoc query is when you stick a bunch of sql statements inside a string in your application. This is bad approach to queries so hopefully that isn't how you built your application. :D — Sean Lange, Feb 09 '17 at 16:55
I've edited some basic sample data into the OP. However, at this point, perhaps it would be easier to just write out the 99 case statements (realistically, closer to 80). Not sure how efficient that would be, but it'd solve my issue I think. — Ryan Cortino, Feb 09 '17 at 16:58
Ahh I didn't see your sample data. You can write our your 300 (each attribute had 3 values) case expressions if you want but what happens when you get a product with 100 attributes? Your query will be broken. — Sean Lange, Feb 09 '17 at 17:01
Yea, I'll keep that in mind. In theory, that won't happen, since the attributes table is limited to a 2-digit numeric key. Realistically, 30 attributes is the most our company provides at this time. — Ryan Cortino, Feb 09 '17 at 17:10

score 1 · Accepted Answer · answered Feb 09 '17 at 17:15

Here is how you can use a dynamic cross tab to accomplish this. If you use this make sure you understand what this is doing. You can uncomment the line before exec at the end to see the dynamic sql this has generated. You can extend this to include new columns for other attribute properties and such if you need to.

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something
(
    ItemRef nvarchar(48)
    , AttributeID numeric(2, 0)
    , AttributeName nvarchar(25)
    , AttributeValue nvarchar(25)
)

insert #Something
select 'Product123', 1, 'Brand', 'MyBrandName' union all
select 'Product123', 2, 'Product Line', 'MyProductLine' union all
select 'Product123', 3, 'Color', 'MyColor' union all
select 'Product456', 1, 'Brand', 'MySecondBrandName' union all
select 'Product456', 2, 'Style', 'MyStyle'

declare @StaticPortion nvarchar(2000) = 
    'with OrderedResults as
    (
        select *, ROW_NUMBER() over(partition by ItemRef order by ItemRef) as RowNum
        from #Something
    )
    select ItemRef';

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by ItemRef order by ItemRef';

with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS 
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)

select @DynamicPortion = @DynamicPortion + 
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then AttributeName end) as AttributeName' + CAST(N as varchar(6)) + CHAR(10) 
     + ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then AttributeValue end) as AttributeValue' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= 
(
    select top 1 Count(*)
    from #Something
    group by ItemRef
    order by COUNT(*) desc
)

declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;

--select @SqlToExecute
exec sp_executesql @SqlToExecute

How to Use Aggregate Case Statements Instead of PIVOT When You Don't Know the Upper Bound?

1 Answers1