change table orientation

I'm looking for a stored procedure what will change the orientation of a
table, so the columns will become the rows and the rows will become the
columns, for example:
Original Table:
StateName StateLongNames StateNumber
========= ============== ===========
CA California 10
FL Florida 20
TX Texas 30
The resulting table should be:
Col1 Col2 Col3 Col4
============== ========== =========== ==========
StateName CA FL TX
StateLongNames California Florida Texas
StateNumber 10 20 30
Note 1: This is not the same as pivot or crosstab tables.
Note 2: No agregated functions are necessary.
Note 3: It has to work on SQL Server 2000.
Thank you,Check this link
http://www.sqlteam.com/item.asp?ItemID=2955
Hope this helps.
--
"wheresaldo" wrote:

> I'm looking for a stored procedure what will change the orientation of a
> table, so the columns will become the rows and the rows will become the
> columns, for example:
> Original Table:
> StateName StateLongNames StateNumber
> ========= ============== ===========
> CA California 10
> FL Florida 20
> TX Texas 30
> The resulting table should be:
> Col1 Col2 Col3 Col4
> ============== ========== =========== ==========
> StateName CA FL TX
> StateLongNames California Florida Texas
> StateNumber 10 20 30
> Note 1: This is not the same as pivot or crosstab tables.
> Note 2: No agregated functions are necessary.
> Note 3: It has to work on SQL Server 2000.
> Thank you,
>|||hi... I saw this article a couple of days ago, it does not what at need, as
it creates a pivot table. what I need is much more simpler, just flit the
orientation of the table and all the data. This is NOT the same as Pivot,
Crosstab, or Transpond procedures, but easily .
Thank you
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||A friend told me to do this, but it's outside my SQL knowledge:
1. Select count(*) for old_table. This will tell you how many columns to
create. Put this count into a variable (or user a cursor)
2. Build the string: "Create table new_table (col" + i + " varchar(50),
col" + ..... And execute it to create your table
3. Select the table structure to get the column names, use a cursor
3.a for each $col_name, Select $col_name, key from old_table order by key,
use cursor2
3.b build the string: "Insert into new_table values (" + $col_name + ", "
+ cursor2_value + ", " + cursor2_value, .... and execute.
3.c repeat loops
Does this make any sense to anyone?
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||You can do this with CASE expressions. See the following article for an
example. A query always returns a fixed number of columns so you will
always need to construct a query to match the appropriate number of
rows, either by hand or using dynamic SQL.
http://support.microsoft.com/defaul...b;EN-US;q175574
Hopefully you realize that this is generally a very silly to even
attempt in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I tried to apply the code you mentioned for the table below and it does not
seem to give the right result. Here is an example table:
CREATE TABLE #States
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO #States VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO #States VALUES ('OH','Ohio',2, 2.25)
INSERT INTO #States VALUES ('CA','California',3, 4.5)
INSERT INTO #States VALUES ('FL','Florida',1, 13.40)
INSERT INTO #States VALUES ('FL','Florida',3, 4.50)
As you can see, there is no unique id to use.
Here is how the table looks like:
StateCode StateName StateNumber StateCost
WI Wisconsin 1 45.11
OH Ohio 2 2.25
CA California 3 4.50
FL Florida 1 13.40
FL Florida 3 4.50
and I need it to look rotated or flipped:
Col1 Col2 Col3 Col3 Col4
WI OH CA FL FL
Wisconsin Ohio California Florida Florida
1 2 3 1 3
45.11 2.25 4.50 13.40 4.50
as you can see, the columns of the resulting table will have to receive
different data types, so I was thinking they can be all vchar(50). Also, as
the number of rows from the original table might vary, the resulting table
might have different amount of columns.
I could do the rotation or flip on the UI or front-end, but the reporting
tool I'm using expects a table... this is why I'm trying to do this on a
Stored Procedure.
I'm surpriced that I have not been able to find this on the web, I think
many people might have the same need.
"David Portas" wrote:

> You can do this with CASE expressions. See the following article for an
> example. A query always returns a fixed number of columns so you will
> always need to construct a query to match the appropriate number of
> rows, either by hand or using dynamic SQL.
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Hopefully you realize that this is generally a very silly to even
> attempt in SQL.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||wheresaldo wrote:
> I tried to apply the code you mentioned for the table below and it does no
t
> seem to give the right result. Here is an example table:
>
I suspect that the reason you find this difficult is that your existing
data doesn't have enough information to solvet he problem. What is the
key of your table? Every table needs a key but you haven't specified
one.
Now, in your rotated output you have put "WI" in column 1 and "OH" in
column 2. But where did you get the idea that "WI" came first and "OH"
came second? That information (1st, 2nd, etc) isn't in your table at
all! So you can't possibly write a generic query to return that result.
Lastly, what is the key in the result you want to ouput? You originally
asked for the column names in col1 but those don't exist in the data
either so you'll have to supply them in your query. Without a key you
may have a hard time constructing any useful information from the
output.
The first example in the link I posted shows how you can do it with
subqueries. By implication you need to supply the missing information
as part of those subqueries.

> I could do the rotation or flip on the UI or front-end, but the reporting
> tool I'm using expects a table... this is why I'm trying to do this on a
> Stored Procedure.
For sure you'll need to supply a tabular result set to the reporting
tool. But every reporting tool I know of will construct this kind of
pivot table from the base data - provided of course that you supply
enough information to complete the task.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you input. Here is the code to rotate (or change the orientation
)
of a table. I'm not sure if it's the most effective and well-written code,
but it does exactly what I need it to do. Hope it can help someone else.
Feedback is welcome:
-- ########################################
######
-- Drop tables if already created.
-- Cannot use temp tables, becuase cannot
-- read structure.
-- ########################################
######
DROP TABLE source
DROP TABLE result
-- ########################################
######
-- create fake source table
-- ########################################
######
CREATE TABLE source
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO source VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO source VALUES ('OH','Ohio',2, 2.25)
INSERT INTO source VALUES ('CA','California',3, 4.5)
INSERT INTO source VALUES ('FL','Florida',1, 13.40)
INSERT INTO source VALUES ('FL','Florida',3, 4.50)
-- ########################################
######
-- Create result table.
-- The result table needs to have the same
-- number of columns as the source table
-- has rows, we do this dynamically.
-- Because a columns in the result table might
-- be pf different data types, each
-- column in the result table is a nvarchar(50).
-- ########################################
######
declare @.counter int
declare @.sql_create_table nvarchar(4000)
declare @.source_total_rows int
select @.source_total_rows=count(*) from source
set @.sql_create_table = 'CREATE TABLE result (columnheader varchar(100)'
set @.counter = 0
while @.counter < @.source_total_rows
begin
set @.counter = @.counter + 1
set @.sql_create_table = @.sql_create_table + ',col' + cast(@.counter as
char(1)) + ' varchar(50)'
end
set @.sql_create_table = @.sql_create_table + ')'
exec (@.sql_create_table)
-- ########################################
######
-- First Loop through the number of column from
-- the source table, we need this to get the
-- column names.
-- Second Loop reads each row from the source,
-- for one column, and then create an insert
-- statement to insert in the result table.
-- ########################################
######
declare @.sql_create_inserts nvarchar(4000)
declare @.source_column_name nvarchar(50)
declare @.source_number_of_columns int
declare @.temp_column_hold nvarchar(50)
SELECT @.source_number_of_columns = count(*)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'source'
set @.counter = 0
while @.counter < @.source_number_of_columns
begin
set @.counter = @.counter + 1
--#####################################
set @.source_column_name = COL_NAME(OBJECT_ID('source'), @.counter)
exec('declare cur cursor for select '+@.source_column_name+' from source')
open cur
fetch next from cur into @.temp_column_hold
set @.sql_create_inserts = 'insert into result values (''' +
@.source_column_name + ''''
while @.@.FETCH_STATUS = 0
begin
set @.sql_create_inserts = @.sql_create_inserts + ',''' +
cast(@.temp_column_hold As varchar(50)) + ''''
fetch next from cur into @.temp_column_hold
end
set @.sql_create_inserts = @.sql_create_inserts + ')'
exec(@.sql_create_inserts)
close cur
deallocate cur
--#####################################
end
select * from result
"David Portas" wrote:

> wheresaldo wrote:
> I suspect that the reason you find this difficult is that your existing
> data doesn't have enough information to solvet he problem. What is the
> key of your table? Every table needs a key but you haven't specified
> one.
> Now, in your rotated output you have put "WI" in column 1 and "OH" in
> column 2. But where did you get the idea that "WI" came first and "OH"
> came second? That information (1st, 2nd, etc) isn't in your table at
> all! So you can't possibly write a generic query to return that result.
> Lastly, what is the key in the result you want to ouput? You originally
> asked for the column names in col1 but those don't exist in the data
> either so you'll have to supply them in your query. Without a key you
> may have a hard time constructing any useful information from the
> output.
> The first example in the link I posted shows how you can do it with
> subqueries. By implication you need to supply the missing information
> as part of those subqueries.
>
> For sure you'll need to supply a tabular result set to the reporting
> tool. But every reporting tool I know of will construct this kind of
> pivot table from the base data - provided of course that you supply
> enough information to complete the task.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I would also take care of the limitations:
- number of columns cannot exceed 1000
- row must fit on a page|||wheresaldo wrote:
> Thanks for you input. Here is the code to rotate (or change the orientati
on)
> of a table. I'm not sure if it's the most effective and well-written code
,
> but it does exactly what I need it to do. Hope it can help someone else.
> Feedback is welcome:
>
This bit is dangerous:

> exec('declare cur cursor for select '+@.source_column_name+' from source')
>
It retrieves the values in an undefined order. In itself that wouldn't
be too bad but because you go on to do the same for each column there
is absolutely no guarantee that the data for columns in the result will
line up with what was in each row in the source. What you should do is
add ORDER BY <some key>... but you still don't have a key in the source
table.
If you did have a key then you wouldn't need nested loops and cursors.
You could use a single query instead, which is generally preferable to
a cursor solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--