September 13, 2011 at 6:32 am
Hi,
I have to transform a recordset like this:
a1 b1 c1 d1
a2 b2 c2 d2
a3 b3 c3 d3
into:
a1 a2 a3
b1 b2 b3
c1 c2 c3
d1 d2 d3
There is no aggregation here, almost all the fields are varchar(...), so this is not a PIVOT...
Now, of course I can write a stored procedure to transform the data as required (but my solution would be rather primitive, and I am sure, pretty slow), my question is if there is something already build for a task like this and I am not aware of it - I remember reading about a "sp_pivot" stored procedure while reading Itzik Ben Gan's "MS Press Inside SQL Server 2008 Programming", but that S.P. is expecting an "aggregation function" as a parameter, and that is not the case here...
September 13, 2011 at 7:04 am
Please have a look at the CrossTab article referenced in my signature.
Even though it has a MAX() aggregation in there, it still provide the results you're looking for.... Don't be too pessimistic about it. Give it a try and you'll see... 😉
September 13, 2011 at 7:08 am
Your requirement isn't very clear from your sample data (it would help if you posted DDL, insert statements etc.) Are you saying you need an unlimited amount of columns (one for every row of data).
It sounds like you may be after a dynamic crosstab/pivot but it's impossible to tell from your example.
I'd recommend looking at Jeff Moden's article on the subject:
http://www.sqlservercentral.com/articles/Crosstab/65048/
Although PIVOT requires an aggregate function to be used, this can be a MIN/MAX over 1 row - an aggregate function is always required to do this in a set-based way.
September 13, 2011 at 7:43 am
I didn't post a specific DDL because I was trying to find out if there is a general solution better than just writing a cursor-based SP of my own...
The schema is basically made of 10-12 fields, all of them char / varchar(100-200)..
And yes, each new row added to the initial data means one new column - it must be some kind of dynamic PIVOT, but there isn't any numeric field in the source data to aggregate against it, and I am not sure if adding a dummy row_number (for example) to the dataset really helps...
September 13, 2011 at 8:14 am
You would have to add a dummy ROW_NUMBER type column and pivot this (either through PIVOT or a "crosstab").
Would be interesting to get some understanding of why you need to do this? Sounds like an unusual requirement...
September 13, 2011 at 9:53 am
I agree it is probably the most weird request ever, it is a request from a client ...
They need one Excel column for each row in the dataset, and at the bottom of the CSV they need to add (manually) other informations for each row...
Can't really argue to the clients....
September 13, 2011 at 1:18 pm
virgilrucsandescu (9/13/2011)
I agree it is probably the most weird request ever, it is a request from a client ...They need one Excel column for each row in the dataset, and at the bottom of the CSV they need to add (manually) other informations for each row...
Can't really argue to the clients....
Excel as the target?
Why not use a macro that performs a simple transpose?
Other than that: have a look at the DynamicCrossTab article referenced in my signature to build a dynamic solution. Once you see the code required to mimic a simple EXCEL command, arguing with the client would be more easy (especially when talking about the additional cost to maintain the code)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply