How to Create Independent Lists of Items in SQL Server Columns

aggregatepivotsql serverssrs

So in my database I have items, and those items have categories.

I've created a small sample data.

enter image description here

I'm creating a report where a user will be able to filter the items by colour, category and some other attributes, and then it should simply list items that fit my parameters in separate category columns.

I also don't know how many categories the user will chose and thus I don't know how many columns my report should have.

enter image description here

This is how a pivot looks like when I apply it to my sample data. This is basically the format that I want, however I want to get rid of the nulls. I need the columns to just 'independently' list the items, like so

enter image description here

Since there was no items from the XX category in my sample dataset, the column is not there. The rest gets 'aggregated'.

Okay so this brings me to my main question. Or questions, rather, I think I have two.

1) I am new to SQL and I'm still learning so correct me if I'm wrong, but am I right to think that this is simply a task that 'should not be performed' in relational databases? I feel like this is going against the core rule, that one row is one piece of data, one entry. Here my one row becomes three (or more/less) pieces of data, and there is no explicit relation to between them.

I previously had to make a report where I displayed two items side by side, the user could pick a shop for the left side, another shop for the right side and see the best selling items from the 'left group' and the best selling items from the 'right group'. So I simply joined the two groups by ROW_NUMBER generated ranking and my data changed from 'one item per entry' to 'Nth posixtion in chosen shops per entry'. That made sense.

This here doesn't, at least to my current understanding. But that brings me to my second question:

2)They still want me to do it. How do I?

So far I have thought of two possible solutions.

One is I pick categories separately, then I join them together based on some kind of ROW_NUMBER, to assure one-to-one relationship (I don't really care about ordering)

So if my desired categories were 'OD', 'AL', 'UT', I'd take take them out with 3 separate queries like so

SELECT
    ROW_NUMBER() OVER (ORDER BY (select null)) AS rn
    ,Item
    ,Category
INTO #OD
FROM 
    sth
WHERE
    Category = 'OD'

And so on for every category, and then join them together using 'rn' column. This works, but because I do not know how many categories the report user will choose, it gets problematic. Perhaps that could be done with dynamic SQL? I'm not sure and I'm not really a big fan of using it…

I've also thought of a different solution but I am not sure if that's doable. If it were possible to somehow assign numbers like this:

enter image description here

So the row number 1 is labeled '1' because it's the first value for 'OD' it encountered. Row 2 is labeled '1' as well because it's the first falue for 'AL' it encountered.
That way row number 4 is labeled '2' and row number 5 is labeled '3' because it's the second and third value for 'OD' column respectively. And then row number 6 is labeled '2' again because it's the second value for the 'AL' column.

That way I could use MAX() function and group by those assigned numbers. All the rows assigned 1 will get aggregated to one row, all the rows assigned 2 will be aggregated to another row etc. I am certain that there will aways be one item per row in my pivot, because an item cannot be of two categories.

I also feel like that solution would be much easier to do in a dynamic way (unknown number of columns – categories chosen) but I cannot figure out how to get such a numbering and if that is even possible.

Looking forward to your answers guys, I'm stuck!

Best Answer

I think this is what you are looking for:

test setup: http://rextester.com/SGKW56535

create table t (Item int, Kategoria char(2))
insert into t values
   (1111,'OD')
 , (2222,'AL')
 , (1234,'UT')
 , (1122,'OD')
 , (4321,'OD')
 , (5555,'AL')
 , (6666,'UT')
 , (65577,'AX');

query with row_number() partitioned by Kategoria:

select 
    rn=row_number() over (
        partition by Kategoria
        order by Item
        )
    , Item
    , Kategoria
  from t;

results:

+----+-------+-----------+
| rn | Item  | Kategoria |
+----+-------+-----------+
|  1 |  2222 | AL        |
|  2 |  5555 | AL        |
|  1 | 65577 | AX        |
|  1 |  1111 | OD        |
|  2 |  1122 | OD        |
|  3 |  4321 | OD        |
|  1 |  1234 | UT        |
|  2 |  6666 | UT        |
+----+-------+-----------+

hard coded pivot example: 

with cte as (
  select 
    rn=row_number() over (
        partition by Kategoria
        order by Item
        )
    , Item
    , Kategoria
  from t
  )
  select rn, OD, AL, UT
  from cte
    pivot (max(Item) for Kategoria in (OD, AL, UT) ) p;

results: 

+----+------+------+------+
| rn |  OD  |  AL  |  UT  |
+----+------+------+------+
|  1 | 1111 | 2222 | 1234 |
|  2 | 1122 | 5555 | 6666 |
|  3 | 4321 | NULL | NULL |
+----+------+------+------+

dynamic pivot example:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ',' + quotename(Kategoria)
      from t 
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
select  @sql = '
 select rn, ' + @cols + '
  from  (
    select 
        Kategoria
      , Item
      , rn=row_number() over (
          partition by Kategoria 
          order by     Item 
          ) 
      from t
      ) as a
 pivot (max([Item]) for [Kategoria] in (' + @cols + ') ) p'
 select @sql
 exec(@sql);

generated sql: 

 select rn, [AL],[AX],[OD],[UT]
  from  (
    select 
        Kategoria
      , Item
      , rn=row_number() over (
          partition by Kategoria 
          order by     Item 
          ) 
      from t
      ) as a
 pivot (max([Item]) for [Kategoria] in ([AL],[AX],[OD],[UT]) ) p

dynamic sql result:

+----+------+-------+------+------+
| rn |  AL  |  AX   |  OD  |  UT  |
+----+------+-------+------+------+
|  1 | 2222 | 65577 | 1111 | 1234 |
|  2 | 5555 | NULL  | 1122 | 6666 |
|  3 | NULL | NULL  | 4321 | NULL |
+----+------+-------+------+------+
Related Question