Creating a finite rollup query
-
OK... The best thing to do is to provide an example of the datatable:
The first column is a unique identifer for the table, the other 3 columns are integers which store GUID row references to other tables
Col0 | Col 1 | Col 2 | Col 3 |
-------|--------|-------|--------|
1 |8989 |null | 4666 |
2 |8989 |7444 | null |
3 |7935 |6444 | 3666 |
4 |7935 |null |null |
5 |7935 |8444 |4666 |
6 |9555 |7444 |5666 |
7 |9555 |9444 |3666 |
8 |9555 |6444 |null |
WHAT I NEED TO GENERATE IS THIS RESULT:
Col1 | Col 2 | Col 3 | Col 4 | Col 5 | Col 6 | Col 7 |
8989 | null | 4666 | 7444 | null | null | null |
7935 |6444 | 3666 | null | null | 8555 | 4666 |
9555 | 7444 | 5666 | 9444 | 3666 | 6444 | null |
The actual order of the column pairs (2/3, 4/5, 6/7) do not matter, but only that the associated columns 2 and 3 are kept together with their respective column 1 ID.
A quick way to generate this data is as follows:
create table #rollupTable (Col0 int , Col1 int, Col2 int, Col3 int)
go
insert into #rollupTable values (1 , 8989, null, 4666 )
insert into #rollupTable values (2 , 8989, 7444, null )
insert into #rollupTable values (3, 7935, 6444, 3666)
insert into #rollupTable values (4, 7935, null, null)
insert into #rollupTable values (5, 7935, 8444, 4666)
insert into #rollupTable values (6, 9555, 7444, 5666)
insert into #rollupTable values (7, 9555, 9444, null)
insert into #rollupTable values (8, 9555, 6444, null)
go
select * from #rollupTable
Summary... I have tried Rollups, cubes, group by, joins, unions, case statements, nested tables and nothing I have tried comes close to what I need to generate. Do you guys have any ideas or suggestions? I am so totally stumped I am not sure where to turn for help. I am sorry if this is a trivial problem - I am betting I have missed something obvious.
Thanks folks for any ideas!! 😀
-
with cte as
(select Col0,Col1,Col2,Col3,
row_number() over(partition by Col1 order by Col0) as rn
from #rollupTable)
select Col1,
max(case when rn=1 then Col2 end) as [Col 2],
max(case when rn=1 then Col3 end) as [Col 3],
max(case when rn=2 then Col2 end) as [Col 4],
max(case when rn=2 then Col3 end) as [Col 5],
max(case when rn=3 then Col2 end) as [Col 6],
max(case when rn=3 then Col3 end) as [Col 7]
from CTE
group by Col1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
what u are looking for is not clear?,
present your clear requirement.
-
Mark - THAT is exactly what I was looking for. Obviously, I have a ton of learning to do as I have not ever used partition before. Thank you! You cannot believe how I truly appreciate your feedback!
-
As I quickly try to summarize what Mark did (hopefully accurately) so that anybody who comes across this posting can have a learning reference point -
The ROW_NUMBER function assigns an ordinal value with each record returned, with the ordinal values depending on a particular ORDER BY clause used in tandem with the function. The syntax for ROW_NUMBER is: ROW_NUMBER() OVER([partition] order-by-clause). Since I was interested in in seeing the top column per unique Col1 entry. We can accomplish this with the PARTITION BY clause in the ROW_NUMBER function. Great basic examples are given here: http://www.4guysfromrolla.com/webtech/010406-1.shtml
Mark, then insightfully, used a case statement to trap the position of the row (provided by the Row_Number w/partition function), and put the proper column value into its correct position in the row.
Obviously this solution works great if you know the maximum number of rows that you will want to pull out into their own columns (in this example, there was a maximum count of 3 rows of the same Col1 in the datatable). In most cases, you will only pull back a known finite number of matches to pivot. But if you wanted a more dynamic solution, then you could create a stored procedure where the max(count(*)) of col1 would be assigned to a variable, and then you would create the syntax through a loop that would be used within the case statement (you would still need to know the number of columns per grouping - e.g. I had 2 columns to transpose for every col1 row). In this case, I would probably use a programming language like C# or VB.Net to do this SQL syntax creation since I am not as good as Mark. But hopefully this helps give enough information to do more research or at least have a clue as to how this all worked.
Thanks again to Mark!!!! :w00t:
BTW...I believe this is specific to SQL2005 and newer - sorry for the folks still running SQL2000 and having this problem. 🙁
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply