Pivotong rows to columns 1

[goofaholix]

I have a complex data structure which I've simplified into a View which looks something like this (This is very simplified, I have over 400 ColumnNames);

UID ColumnName DateValue StringValue IntValue TextValue
1234 StartDate 30/04/2008
1234 Name Jim Bloggs
1234 Age 30
1234 Description blah blah

I've created a table to receive the data which will look something like this when the data is added;

UID StartDate Name Age Description
1234 30/04/2008 Jim Bloggs 30 Blah Blah

I've experimented with using the Pivot function and it will work for all datatypes except the Text columns.

I've tried rolling up DateValue, StringValue, IntValue TextValue into one column before I pivot but I have to convert the data types to varchar and the Text column ends up being truncated.

I tried doing a dynamic pivot in a stored procedure when the query is run but I need to view the field list in Reporting Services and Reporting Services doesn't automatically create these when the columns are dynamic. Mnaually creating hundreds of fields in each report isn't an option.

Is there any other way?

I wondered if a dynamic update statement was possible. Something like;

Update table set column = textvalue
where column = columnname and UID = UID

It would need to be dynamic as I don't want to hard code 100+ of these for each ciolumn that has a datatype of Text.

Thanks

bruce

 

[RiverGuy]

See the following thread

thread183-1479704

 

[goofaholix]

That thread doesn't do the trick. The answers are about concatenating several values into a single column whereas I want to insert those values into different columns.

Thanks

Bruce

 

[RiverGuy]

Sorry I just looked at the thread title and suggested in the other thread to look at the FAQ's and previous posts for crosstab queries as these questions are asked very frequently.

Anyways, are you using SQL 2005? If so, you can try casting to VARCHAR(MAX) which shouldn't truncate your TEXT column.

 

[quatermass]

Hi Bruce,

I haven't done anything with the number of columns you have, I have only had to do 7/12 for days/months, but....

What I do is to create a table with a single column and populate that column with 1-7 or 1-12 as required.

Next, I run a cross-join query against the original table and the new table just created. This results in each record in the original table being duplicated 7/12 times.

As part of this query, I put in 7/12 CASE statements, which will return the data on a single instance of the record.


Eg

Original_Table

ID, Day, Value
1, Mon, 1
1, Tue, 10
2, Mon, 5
2, Tue, 8
....

New_Table

DayNo
1
2
3
....

When running the initial query outlined above

select t1.ID, 
case when t2.DayNo = 1 then t1.Value else 0 end as Mon_Val,
case when t2.DayNo = 2 then t1.Value else 0 end as Tue_Val,
....

from Original_Table t1, New_Table t2

This will result in a result set as follows:

ID, Mon_Val, Tue_Val, .....
1, 1, 0, ....
1, 0, 10, ....
2, 5, 0, ....
2, 0, 8, ....

This record set then just needs another query run on it to amalgamate the single values on multiple records to multiple values on a single record

select ID, max(Mon_Val) as Mon, max(Tue_Val) as Tue....
from query
group by ID

(It is possible to put both of these queries in a single statement, but I have kept them separate to show what is happening)


The output of this second query is:

ID, Mon, Tue....
1, 1, 10 ....
2, 5, 8 ....

With the number of columns you need to create, it may be easier to try to use syscols to help generate the case statement.


Hope it helps

**************************************************************
Rock is Dead (Long Live Paper and Scissors)
**************************************************************