horizontal recordset to vertical recordset
-
I would like to take the results of an sql statement that are returned horizontally and place them in a vertical order. For example,
SELECT Top 1 LastName, FirstName, Gender, Age
FROM Client
The standard results would look something like:
LastName FirstName Gender Age
Smith Joe M 32
Is there a way, without using a cursor where the results could instead be displayed as:
FieldName Value
LastName Smith
FirstName Joe
Gender M
Age 32
This really should be a function that will accept a recordset with a variety of possible column headers. One recordset may have 4 columns and another may have 10, but all would return only one data record.
I have tried using syscolumns, but haven't been able to get it to work.
Thanks,
Keoki
-
I am using the SQL Server 2005 CTP and it has some nice new TSQL features like PIVOT and UNPIVOT which take care of this very nicely. However, I have not found a way to do this within SQL Server 2000 or earlier. When I have really needed to do this I ended up creating a pivot table in Excel using a data link to SQL Server and doing it that way.
-
Unfortunatelly you have to perform a union
Select 'First Name' as FieldName, FirstName as Value
From mytable
where id = 1
union
Select 'Last Name', LastName
From mytable
where id = 1
Select 'Gender', Gender
From mytable
where id = 1
Select 'Age', Age
From mytable
where id = 1
-
Does this work for you?
USE tempdb
IF OBJECTPROPERTY(OBJECT_ID('dbo.MyPivot'), 'IsUserTable') = 1
DROP TABLE dbo.MyPivot;
GO
CREATE TABLE dbo.MyPivot
(
ID INT NOT NULL
, LastName VARCHAR(20) NOT NULL
, FirstName VARCHAR(20) NOT NULL
, Gender CHAR(1) NOT NULL
, Age TINYINT NOT NULL
);
GO
INSERT INTO MyPivot VALUES (1, 'Smith','Joe','M',32)
INSERT INTO MyPivot VALUES (2, 'Baker','Mary','F',26)
CREATE TABLE dbo.#MySchema
(
objectid INT NOT NULL,
attribute NVARCHAR(30) NOT NULL
PRIMARY KEY (objectid, attribute)
);
GO
-- This assumes the ID is Ordinal_Position = 1 and isn't required in the output
INSERT INTO dbo.#MySchema
SELECT Ordinal_Position, Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_Name='MyPivot' AND Ordinal_Position > 1 ;
GO
SELECT attribute, MyValue =
CASE attribute
WHEN 'LastName' THEN CAST(LastName as SQL_VARIANT)
WHEN 'FirstName' THEN CAST(FirstName as SQL_VARIANT)
WHEN 'Gender' THEN CAST(Gender as SQL_VARIANT)
WHEN 'Age' THEN CAST(Age as SQL_VARIANT)
END
FROM #MySchema CROSS JOIN MyPivot
ORDER BY ID, Objectid
DROP TABLE dbo.#MySchema;
-
That would work except for the fact that it needs to be dynamic. It is quite possible the names and number of columns to be converted would be different from one run to the next.
-
i searched for "pivot" here on SSC, and found a number of script contributions; you should look at those as well;
this link looked promising: http://qa.sqlservercentral.com/scripts/contributions/1038.asp
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have added the dynamic element you need, hopefully this will be closer to what you need.
Youwill need to know the name of the field which uniquely identifies each row in the input table or view.
USE Tempdb
GO
--Create the Data
IF OBJECTPROPERTY(OBJECT_ID('dbo.MyData'), 'IsUserTable') = 1
DROP TABLE dbo.MyData;
GO
CREATE TABLE dbo.MyData
(
ID INT NOT NULL
, LastName VARCHAR(20) NOT NULL
, FirstName VARCHAR(20) NOT NULL
, Gender CHAR(1) NOT NULL
, Age TINYINT NOT NULL
);
GO
INSERT INTO MyData VALUES (1, 'Smith','Joe','M',32)
INSERT INTO MyData VALUES (2, 'Baker','Mary','F',26)
GO
-- Create stored proc which will create the pivoted table
-- Input Parameters
-- 1 name of the table or view containing the data
-- 2 Field Name which will uniquely identify each row in table or view(only 1 in this example)
-- 3 name of the output table
CREATE PROC #usp_Pivot
(
@Table VARCHAR(40)
, @ID VARCHAR(40)
, @Pivot VARCHAR(40)
) AS
DECLARE
@Sql varchar(8000)
, @Cnt int
, @Max int
IF OBJECTPROPERTY(OBJECT_ID('MyPivot'), 'IsUserTable') = 1
EXECUTE ('DROP TABLE ' + @Pivot )
CREATE TABLE dbo.#MySchema
(
objectid INT NOT NULL,
attribute NVARCHAR(30) NOT NULL
PRIMARY KEY (objectid, attribute)
 
;-- Timmestamp cannot be converted to string so is excluded, maybe others?
INSERT INTO dbo.#MySchema
SELECT Ordinal_Position, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_Name = @Table AND data_type <> 'TIMESTAMP'
SET @Max = (SELECT COUNT(*) FROM #MySchema)
SET @Cnt = 1
SET @Sql = 'SELECT ' + @ID + ' , attribute, MyValue =
CASE objectid'
WHILE @Cnt < @max-2 + 1
BEGIN
SET @Sql = @Sql + ' WHEN ' + CAST(@Cnt AS char(3)) + ' THEN CAST('
SET @Sql = @Sql + (SELECT attribute FROM #MySchema WHERE objectid = @Cnt)
SET @Sql = @Sql + ' as SQL_VARIANT) '
SET @Cnt = @Cnt + 1
END
SET @Sql = @Sql + 'END
INTO ' + @Pivot + '
FROM #MySchema CROSS JOIN ' + @Table + '
ORDER BY ' + @ID + ' , objectid;'
EXECUTE (@Sql);
DROP TABLE dbo.#MySchema;
GO
-- End Stored proc creation
-- Run the stored proc
EXEC #usp_Pivot 'MyData','ID','MyPivot'
DROP PROCEDURE #usp_Pivot
-- display the pivot info
SELECT * FROM MyPivot
-
Sorry small error, left in the literal 'MyPivot' instead of @Pivot.
USE Tempdb
GO
--Create the Data
IF OBJECTPROPERTY(OBJECT_ID('dbo.MyData'), 'IsUserTable') = 1
DROP TABLE dbo.MyData;
GO
CREATE TABLE dbo.MyData
(
ID INT NOT NULL
, LastName VARCHAR(20) NOT NULL
, FirstName VARCHAR(20) NOT NULL
, Gender CHAR(1) NOT NULL
, Age TINYINT NOT NULL
);
GO
INSERT INTO MyData VALUES (1, 'Smith','Joe','M',32)
INSERT INTO MyData VALUES (2, 'Baker','Mary','F',26)
GO
-- Create stored proc which will create the pivoted table
-- Input Parameters
-- 1 name of the table or view containing the data
-- 2 Field Name which will uniquely identify each row in table or view(only 1 in this example)
-- 3 name of the output table
CREATE PROC #usp_Pivot
(
@Table VARCHAR(40)
, @ID VARCHAR(40)
, @Pivot VARCHAR(40)
) AS
DECLARE
@Sql varchar(8000)
, @Cnt int
, @Max int
IF OBJECTPROPERTY(OBJECT_ID(@Pivot), 'IsUserTable') = 1
EXECUTE ('DROP TABLE ' + @Pivot )
CREATE TABLE dbo.#MySchema
(
objectid INT NOT NULL,
attribute NVARCHAR(30) NOT NULL
PRIMARY KEY (objectid, attribute)
 
;-- Timmestamp cannot be converted to string so is excluded, maybe others?
INSERT INTO dbo.#MySchema
SELECT Ordinal_Position, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_Name = @Table AND data_type <> 'TIMESTAMP'
SET @Max = (SELECT COUNT(*) FROM #MySchema)
SET @Cnt = 1
SET @Sql = 'SELECT ' + @ID + ' , attribute, MyValue =
CASE objectid'
WHILE @Cnt < @max-2 + 1
BEGIN
SET @Sql = @Sql + ' WHEN ' + CAST(@Cnt AS char(3)) + ' THEN CAST('
SET @Sql = @Sql + (SELECT attribute FROM #MySchema WHERE objectid = @Cnt)
SET @Sql = @Sql + ' as SQL_VARIANT) '
SET @Cnt = @Cnt + 1
END
SET @Sql = @Sql + 'END
INTO ' + @Pivot + '
FROM #MySchema CROSS JOIN ' + @Table + '
ORDER BY ' + @ID + ' , objectid;'
EXECUTE (@Sql);
DROP TABLE dbo.#MySchema;
GO
-- End Stored proc creation
-- Run the stored proc
EXEC #usp_Pivot 'MyData','ID','MyPivot'
DROP PROCEDURE #usp_Pivot
-- display the pivot info
SELECT * FROM MyPivot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply