Converting N rows to columns, including custom headers
-
Hello,
I have been stuck on this for a long time now, I hope that someone can help!
Please refer to this SQLFiddle for the table structure: Link to FiddleSQL server 2008 used.
Goal:
I need to create a raw data report from several tables like this one. Below template is the only way I have found so far that could allow the data to be usable for further analysis after export. Please note that there are 2 different types: 'IN' and 'EX' that I would need to display as in the below example. I would then also need to rename the Type column to something like 'Internal Check', for the 'IN' Type.
Template:
I hope the idea is clear, if not please let me know, I can update this post and / or the related fiddle.
In addition to above request, I would also like to know how I could incorporate the result to another query, basically doing a join on SN.Many thanks in advance!
-
Ideally, don't do this in SQL Server but in your presentation layer. Things like SSRS (using a Matrix) or Microsoft Excel (using a Pivot Table) is far easily. Is this something you can entertain?
Also, that image is a little hard to read; you've not cut it that well. You would be better supplying your expected output for your full sample data though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hi Thom,
Many thanks for you answer! I have already re-uploaded the picture, I think it was the forum's preview function that cut too much into it (it was fine on my end).
Actually I started out on SRSS, I already had a huge query made with query builder, where I had all required elements. The problem I encountered there was that I had duplicate SNs because of the 'IN' and 'EX'. Since this report would need to have pictures in it and also needs to be generated on demand, I think it would make sense to have it on SRSS. Especially since at a later stage we may want to do further analysis on this data, which could probably be done through a dashboard allowing a user to create custom reports, but that is science fiction for now. First need to get this thing off the ground.
Could you please elaborate on how to achieve the required transformation in SRSS? Can I get this done from a complete query that joins all required tables together? Or do I need to do it in separate queries, transform the data in SRSS and then display it (re-join it before that)?
At the moment I am trying to get my first report done in Pentaho Kettle, this is why I need the SQL. Right now this is the tool I know the best, but unfortunately the rows denomraliser step it limited to static data.
Supplying the whole data set would be complicated, since it is quite big. In principle it is made of single value entries and then multi-value entries that need to be transformed to columns in order to be able to display them in a report.
Any hints you could give here would be greatly appreciated!
Many thanks in advance!
- baoshenglishanghai - Monday, May 21, 2018 12:44 AMHello,
I have been stuck on this for a long time now, I hope that someone can help!
Please refer to this SQLFiddle for the table structure: Link to FiddleSQL server 2008 used.
Goal:
I need to create a raw data report from several tables like this one. Below template is the only way I have found so far that could allow the data to be usable for further analysis after export. Please note that there are 2 different types: 'IN' and 'EX' that I would need to display as in the below example. I would then also need to rename the Type column to something like 'Internal Check', for the 'IN' Type.
Template:

I hope the idea is clear, if not please let me know, I can update this post and / or the related fiddle.
In addition to above request, I would also like to know how I could incorporate the result to another query, basically doing a join on SN.Many thanks in advance!
Heh... no... not going to SQL Fiddle to see your table structure. Attach the CREATE TABLE statement and other goodies to your post. That way, I don't have to fart around with something external and when you decide to take it off of SQL Fiddle, it won't destroy the helpfulness that this thread could become to others.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply