February 18, 2009 at 12:12 pm
Hey,
I need to PIVOT a table, but I don't actually need to aggregate any data. The reason I am doing this is because I want to save some records to XML. For example, I want to change this:
CREATE TABLE MyTable(
col1 int,
col2 int,
col3 int
);
INSERT INTO MyTable
SELECT 1, 0, 0 UNION
SELECT 0, 1, 0;
SELECT TOP 1 * FROM MyTable;
to be XML like this:
{Table name="MyTable"}
{col Name="col1"}1{/col}
{col Name="col1"}0{/col}
{col Name="col1"}0{/col}
{/Table}
Note: using actual XML brackets in this post returns XML that is rendered by my browser, even in IFCode Code tags...., used {}'s instead
I chose this format because it is easily parseable for me and my developers, as opposed to FOR XML AUTO RAW/EXPLICIT and all that.
Questions? Comments?
---
Dlongnecker
February 18, 2009 at 12:20 pm
How would it look if you wanted two rows ?
* Noel
February 18, 2009 at 12:26 pm
I'm guaranteed to only deal with one row at a time - We're still working out the exact structure of how the data comes in and all but it's not a concern.
I meant to mention before, the way I want the data to be returned is:
COL_NAME COL_VALUE
----------------
COL1 1
COL2 0
COL3 0
I think I'm ultimately planning on using the results and placing them into a FOR XML explicit, but that's not really of concern.... I think. If I can be shown how to pivot the data to give me an output like this, I think I can show how it will all fit together.
---
Dlongnecker
February 18, 2009 at 12:44 pm
[font="Verdana"]I think you actually want to unpivot your data, rather than pivot it. It's already pivoted. So you would end up with something like this:
select row_num, [col_name], col_value
from (select row_number() over (order by col1) as row_num, col1, col2, col3 from MyTable) piv
unpivot (col_value for [col_name] in (col1, col2, col3)) as unpiv
[/font]
February 18, 2009 at 12:52 pm
This is more of an UNPIVOT question.
SELECT ColumnName,
CASE ColumnName WHEN 'Col1' THEN col1
WHEN 'Col2' THEN col2
ELSE col3 END AS ColumnValue
FROM MyTable
CROSS JOIN (
SELECT 'Col1' AS ColumnName
UNION ALL
SELECT 'Col2' AS ColumnName
UNION ALL
SELECT 'Col3' AS ColumnName
) c
WHERE col1=1
Col1 1
Col2 0
Col3 0
I'll leave the detailed formatting to produce valid XML for you to deal with.
February 18, 2009 at 1:00 pm
Good good,
Whenever I try these with the actual table, I get the following error:
Msg 8167, Level 16, State 1, Line 1
The type of column "app_sys_group_cde" conflicts with the type of other columns specified in the UNPIVOT list.
Here is the actual table definition I am using, and SQL statement I jurrigged:
CREATE TABLE _SOLUTION(
SOLUTION_CDE char(5) primary key,
APP_SYS_GROUP_CDE tinyint null
)
select row_num, [col_name], col_value
from (select row_number() over (order by solution_cde) as row_num, solution_cde, app_sys_group_cde from _SOLUTION) piv
unpivot (col_value for [col_name] in (solution_cde, app_sys_group_cde)) as unpiv
[/CODE]
(this is a smaller version of the table, but reproduces the error)
---
Dlongnecker
February 18, 2009 at 1:32 pm
Scott answer should be all you need.
Have you tried it ?
* Noel
February 18, 2009 at 1:43 pm
[font="Verdana"]It's a type clash between the char(5) for solution_cde and the tinyint for app_sys_group_cde. You need to cast all of the columns that are going to be listed to the same type. So in this example, here's my test code:
CREATE TABLE _SOLUTION(
SOLUTION_CDE char(5) primary key,
APP_SYS_GROUP_CDE tinyint null
);
insert into _SOLUTION
select 'abcde', 1 union all
select 'vwxyz', 2;
select row_num, [col_name], col_value
from (select row_number() over (order by solution_cde) as row_num, cast(solution_cde as varchar(5)) as solution_cde, cast(app_sys_group_cde as varchar(5)) as app_sys_group_cde from _SOLUTION) piv
unpivot (col_value for [col_name] in (solution_cde, app_sys_group_cde)) as unpiv
go
drop table _SOLUTION
[/font]
February 23, 2009 at 7:47 am
I'm a moron.
Thanks for the help.
---
Dlongnecker
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply