May 8, 2009 at 12:56 pm
I have a Table with one long row of data and I need to transpose all of my Column headers into Rows. The headers need to become the ID. The problem is the the Original Table is constantly changing (adding Fields, deleting fields and renaming fields) so i cant just write out a union Query. I need to script it because I never know the exact number of fields or the field names at any given moment.
Any ideas would be greatly appreciated. This Database
Sample Table Data:
ID | pstt375 | pstt455 | pstt585 | pstt643 | phyl375 |.....| pwml306
------------------------------------------------------------------
1 5 45 0 34 2 .... 1
Need:
pstt375 | 5
pstt455 | 45
pstt585 | 0
pstt643 | 34
phyl375 | 2
...
...
...
...
pwml306 | 1
May 11, 2009 at 12:52 pm
There is probably a better way to do this, without using dynamic SQL, but here is one way to do it. You said your table has one long row. Did you mean that it has *exactly* one long row, and no more? If so, the below will work for you.
First, set up the test table and populate the data
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE Test
(
ID INT,
pstt375 INT,
pstt455 INT,
pstt585 INT,
pstt643 INT,
phyl375 INT,
pwml306 INT
)
INSERT INTO test
SELECT 1,5,45,0,34,2,1
Next, set up two temp tables used to store stuff, and populate one of them. The first one, #cols, will be used to store all the column names of your table. The second one, #final, will be used to store your final results, and will be populated by the loop.
IF OBJECT_ID('TempDB..#cols','u') IS NOT NULL
DROP TABLE #cols
CREATE TABLE #cols
(
column_name VARCHAR(128),
ordinal_position INT,
counted BIT DEFAULT(0)
)
INSERT INTO #cols(column_name,ordinal_position)
SELECT
column_name,
ordinal_position
FROM information_schema.columns
WHERE table_name = 'test'
IF OBJECT_ID('TempDB..#final','u') IS NOT NULL
DROP TABLE #final
CREATE TABLE #final
(
ColName VARCHAR(128),
VALUE INT
)
Now, declare some variables, and run the dynamic SQL to populate the second temp table.
DECLARE @Counter INT
SELECT @Counter = 1
DECLARE @MaxCount INT
SELECT @MaxCount = (SELECT MAX(ordinal_position) FROM #cols)
DECLARE @OrdPos INT
DECLARE @ColName VARCHAR(128)
DECLARE @SQL VARCHAR(4000)
WHILE @Counter < @MaxCount
BEGIN
SELECT @OrdPos = (SELECT MIN(ordinal_position)
FROM #cols
WHERE column_name 'ID'
AND counted = 0)
SELECT @ColName = (SELECT column_name
FROM #cols
WHERE ordinal_position = @OrdPos)
SELECT @SQL = 'INSERT INTO #final SELECT ''' + @ColName + '''' + ' AS COL, '
+ @ColName + ' AS VALUE FROM test'
EXEC(@SQL)
UPDATE #cols
SET counted = 1
WHERE ordinal_position = @OrdPos
SELECT @Counter = @Counter + 1
END
Now, see the results as you requested
SELECT
*
FROM #final
*IMPORTANT* this will not work if your table has more than one row. If your table has more than one row, and you want to explore this approach further, post back and let me know, since we will have to deal with the id's of the rows. I'm not sure exactly how that will work, but I'm thinking it will be ugly.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 12, 2009 at 9:08 am
Ok, I took the liberty of assuming the possibility of there being more than one row in your table is too great to ignore. I also took the liberty of assuming you might be interested in my solution. It was good practice, so here it is. I tried to clean up the final select a bit by breaking it into parts, and assigning variables to them. I had to add one more temp table to store all the id's in your table. If temp tables are not possible for you, you could probably use table variables instead. Anyhow, see if this works for you.
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE Test
(
ID INT,
pstt375 INT,
pstt455 INT,
pstt585 INT,
pstt643 INT,
phyl375 INT,
pwml306 INT
)
INSERT INTO test
SELECT 1,5,45,0,34,2,1 UNION ALL
SELECT 2,6,46,1,35,3,2 UNION ALL
SELECT 3,7,47,2,36,4,3 UNION ALL
SELECT 4,8,48,3,37,5,4 UNION ALL
SELECT 5,9,49,4,38,6,5 UNION ALL
SELECT 6,10,50,5,39,7,6
IF OBJECT_ID('TempDB..#cols','u') IS NOT NULL
DROP TABLE #cols
CREATE TABLE #cols
(
column_name VARCHAR(128),
ordinal_position INT,
counted BIT DEFAULT(0)
)
INSERT INTO #cols(column_name,ordinal_position)
SELECT
column_name,
ordinal_position
FROM information_schema.columns
WHERE table_name = 'test'
--New temp table to store each id in test table, and a column
--to keep track of which ones have been used
IF OBJECT_ID('TempDB..#id','u') IS NOT NULL
DROP TABLE #id
CREATE TABLE #id
(
ID INT,
Complete BIT DEFAULT(0)
)
INSERT INTO #id(id)
SELECT
ID
FROM test
ORDER BY id
IF OBJECT_ID('TempDB..#final','u') IS NOT NULL
DROP TABLE #final
CREATE TABLE #final
(
ID VARCHAR(20),
ColName VARCHAR(128),
VALUE INT
)
--Need to set up counters
DECLARE @Counter INT
SELECT @Counter = 1
DECLARE @MaxCount INT
SELECT @MaxCount = (SELECT MAX(ordinal_position)FROM #cols)
DECLARE @id VARCHAR(20)
DECLARE @OrdPos INT
DECLARE @ColName VARCHAR(128)
DECLARE @ColString VARCHAR(130)
--I find it easier to assign variables to the string parts of the final SELECT
--when/if it gets to out of hand, like this one did, then put them together
--before executing
DECLARE @SQL1 VARCHAR(4000)
SELECT @SQL1 = 'INSERT INTO #final SELECT '
DECLARE @SQL2 VARCHAR(400)
SELECT @SQL2 = ' AS VALUE FROM test WHERE id = '
DECLARE @SQL3 VARCHAR(4000)
--You will need two loops. One to keep track of the id's from test table that
--have been inserted so far, and one to keep track of which fields have been
--inserted for each id. Someone could probably figure out a way to do it
--all in one loop, but I could not make it work that way.
--First loop will run as long as there are id's in #id that are not complete
WHILE EXISTS (SELECT*FROM #id WHERE complete = 0)
BEGIN
SELECT @id = (SELECT MIN(id) FROM #id
WHERE complete = 0)
WHILE @Counter < @MaxCount
BEGIN
SELECT @OrdPos = (SELECT MIN(ordinal_position)
FROM #cols
WHERE column_name 'ID'
AND counted = 0)
SELECT @ColName = (SELECT column_name
FROM #cols
WHERE ordinal_position = @OrdPos)
--@ColString holds the string value of each column name
SELECT @ColString = ', ''' + @ColName + '''' + ' AS COL, '
--Now put it all together
SELECT @SQL3 = @SQL1 + @id + @ColString + @ColName + @SQL2 + @id
EXEC(@SQL3)
--Update #cols so the next column name will be selected on the next run
UPDATE #cols
SET counted = 1
WHERE ordinal_position = @OrdPos
--Increment the counter
SELECT @Counter = @Counter + 1
END
--Mark the first id in #id as complete
UPDATE #id
SET complete = 1
WHERE id = @id
--Reset #cols to show no columns have been used for the next id in #id
UPDATE #cols
SET counted = 0
--Reset the counter again
SELECT @counter = 1
END
DROP TABLE #id
DROP TABLE #cols
SELECT
*
FROM #final
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 13, 2009 at 3:56 pm
Well, I guess I can now also take the liberty of assuming maybe this is not what the OP was looking for. (crickets chirping)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 14, 2009 at 6:30 am
Sorry Greg, I was out of the office. I hope to try out your solution by the end of the day. I'll post to let you know the outcome. Thanks for the reply.
May 14, 2009 at 8:23 am
Here is a solution with no temp tables or table variables.
As mentioned in my other post I found this sometime ago and customized it to my needs:
SET NOCOUNT ON
--SAMPLE TABLE
CREATE TABLE Test
(
ID INT,
pstt375 INT,
pstt455 INT,
pstt585 INT,
pstt643 INT,
phyl375 INT,
pwml306 INT
)
--SAMPLE DATA
INSERT INTO test
SELECT 1,5,45,0,34,2,1 UNION ALL
SELECT 2,6,46,1,35,3,2 UNION ALL
SELECT 3,7,47,2,36,4,3 UNION ALL
SELECT 4,8,48,3,37,5,4 UNION ALL
SELECT 5,9,49,4,38,6,5 UNION ALL
SELECT 6,10,50,5,39,7,6*/
SELECT * FROM test
DECLARE @TableName sysname
DECLARE @IdFieldName sysname
DECLARE @SQL varchar(8000)
SELECT
@TableName = 'test',
@IdFieldName = 'ID'
-- create the schema of the resulting table
SET @SQL = 'SELECT TOP 0 CONVERT(int,0) AS [ID], '
+'CAST(0 AS nvarchar(4000)) AS [Col],'
+' CONVERT(sql_variant,N'''') AS [Value] WHERE 1=0 '+CHAR(10)
SELECT @SQL = @SQL + 'UNION ALL SELECT ' + @IdFieldName + ', N'''
+ COLUMN_NAME + ''',CONVERT(sql_variant, '
+ '[' + COLUMN_NAME + ']) FROM [' + @TableName + '] WHERE [' + COLUMN_NAME + '] IS NOT NULL '
+ CHAR(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME @IdFieldName
ORDER BY COLUMN_NAME
EXEC(@sql + ' ORDER BY Id')
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 14, 2009 at 9:12 am
Chris, this seems like a much better solution than mine. After messing around with it a bit, I can understand what it is doing for the most part, but I can not understand how this...
SELECT @SQL = @SQL + 'UNION ALL SELECT ' + @IdFieldName + ', N'''
+ COLUMN_NAME + ''',CONVERT(sql_variant, '
+ '[' + COLUMN_NAME + ']) FROM [' + @TableName + '] WHERE ['
+ COLUMN_NAME + '] IS NOT NULL '+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME @IdFieldName
knows how to put together all the columns without a loop of some sort. I'm stumped. And I re-formated it to make it easier for me to see it.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 14, 2009 at 9:15 am
It's basically because the table is returning a row for each column name and building a string by adding a new union the SQL variable for each row returned
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy