So in my database I have items, and those items have categories.
I've created a small sample data.
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.
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
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:
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!