01/20/2006 11:43 AM
|
Superman_SQL
Posts: 417
Joined: 04/04/2002
|
How can I transpose the data from this query into columns. I know I did this before but can't remember how I did it.
SELECT Field1 FROM Table1
Result
------
A
B
C
Want it to return as
A B C
-------------------------
Clark SQL Elites
|
|
01/20/2006 01:02 PM
|
Superman_SQL
Posts: 417
Joined: 04/04/2002
|
This is the style that I am currently using. The problem is, the number of records varies from 1 to 100. The example that they provided in BOL is more or less, hard coded on the number of fields.
I am using a loop to build it dynamically and run the t-sql in a EXEC. I am not sure if this is the best practice.
Example from BOL:
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
-------------------------
Clark SQL Elites
|
|
01/20/2006 01:44 PM
|
Superman_SQL
Posts: 417
Joined: 04/04/2002
|
Just want to say thank you to RM and Nosepicker for the constant help.
Anyway, I will give a simple example to make easier to read, Nosepicker.
I am going to query against one field in a table. The result set will be something like this:
Field1
-------
A
B
C
D
E
Basing from this data, I just want to create a temp table or even a join of some type to become something like this:
A - B - C - D - E
So, Field1 is "A", Field2 is "B", Field3 is "C" and so on.
Sometime, I might have 5 records, sometime more or less.
Is this the data you are talking about Nosepicker?
-------------------------
Clark SQL Elites
|
|
01/20/2006 06:10 PM
|
nosepicker
Posts: 1100
Joined: 11/04/2003
|
When I asked for more data, I thought you were going to have other data upon which to base your CASE statements. But I see you just want to flatten a one column table. Perhaps this can work for you:
DECLARE @max_nbr_cols int,
@counter int,
@sql varchar(2000)
CREATE TABLE #tmp_data_hold (
counter int identity(1, 1) NOT NULL,
field1 char(1) NULL)
INSERT INTO #tmp_data_hold
SELECT * FROM YourTable
SELECT @max_nbr_cols = COUNT(*) FROM YourTable
SET @counter = 1
SET @sql = 'CREATE TABLE #tmp_pivot_table ('
WHILE @counter <= @max_nbr_cols
BEGIN
SET @sql = @sql + ' col' + CONVERT(varchar, @counter) + ' varchar(10) NULL,'
SET @counter = @counter +1
END
SET @sql = STUFF(@sql, LEN(@sql), 1, ')')
EXEC(@sql)
SET @counter = 1
SET @sql = 'INSERT INTO #tmp_pivot_table SELECT '
WHILE @counter <= @max_nbr_cols
BEGIN
SET @sql = @sql + 'MAX(CASE counter WHEN ' + CONVERT(varchar, @counter) + ' THEN field1 ELSE '''' END) AS col' + CONVERT(varchar, @counter) + ', '
SET @counter = @counter +1
END
SET @sql = STUFF(@sql, LEN(@sql), 1, '')
SET @sql = @sql + ' FROM #tmp_data_hold'
EXEC(@sql)
--DROP TABLE #tmp_data_hold
SELECT * FROM #tmp_pivot_table
Basically, this code takes YourTable's data and inserts it into a temp table with an identity column. Then it dynamically creates another temp table whose number of columns is based on the number of records in YourTable. Then it dynamically creates a SELECT statement that flattens the table.
To better see what it does, comment out the "EXEC(@sql)" commands and substitute "PRINT @sql".
Edited: 01/20/2006 at 06:16 PM by nosepicker
|
|
12/12/2007 05:02 PM
|
Pro Pete
Posts: 5093
Joined: 04/03/2002
|
The problem is that the temp table is being created by the first dynamic sql statement. This means it is not accessible to the second dynamic sql statement nor to the final SELECT statement.
In order for it to be available, both the creation and use must be in the same dynamic SQL statement. To achieve this, the line
SET @sql = 'INSERT INTO #tmp_pivot_table SELECT '
should be replaced by
SET @sql = @sql + 'INSERT INTO #tmp_pivot_table SELECT '
(strictly, the first "EXEC (@sql)" statement should also be commented out, but it will not cause any problem if it isn't.
The final select should also be moved into the end of the dynamic sql as:
SET @sql = @sql + ' FROM #tmp_data_hold
SELECT * FROM #tmp_pivot_table'
However, to be more useful to you, you probably need to be able to access the results from the code that generates and executes the dynamic sql. To achieve this you would need to use a temporary 'permanent' table. To do this, create the table in a database (could be tempdb) without the initial # symbol (e.g. CREATE TABLE tempdb.dbo.tmp_pivot_table) and rop it when you have finished with the data. If the code is to be executed concurrently by multiple users, then the table name will need to be modified to include some unique identification (e.g. the spid of the executing process).
Kalman's linked example solves a slightly different problem, but illustrates use of the PIVOT operator introduced in SQL2005.
-------------------------
Peter Kavanagh
MCDBA, MCSE
SQL Server Magazine Forum Pro
Edited: 12/12/2007 at 05:05 PM by Pro Pete
|
|
|
FuseTalk Enterprise Edition - © 1999-2010 FuseTalk Inc. All rights reserved.
|
|
new topic
search
help
home
login
