Create Table from Row Data

Hello,
In SQL Server 2000, is it possible to take a table with one field (column), and pivot the table so that the characters in the row data become the field (column) names of another table ( or in a View)? The number of records could vary.

If so, how would I do this?

Sample table;
Create Table dbo.MonthlyData
(
Categories varchar(30) NOT NULL
)

Sample data;

Sales Volume 2005-02
TotRefVol 2005-02
Sales Ratio 2005-02
Sales Volume 2005-03
TotRefVol 2005-03
Sales Ratio 2005-03
Sales Volume 2005-04
TotRefVol 2005-04
Sales Ratio 2005-04

If I am following you correctly; Are you wanting Sales Volume,etc.. to be a column name in a view or table?|||

First; Sorry, the subject should have been 'Create table fields from row data'. To answer your question, each row of sample data is contained within a single column called 'Categories'.

|||

Assuming you had some other value to use with the columns you reference you could do something like:

Create Table dbo.MonthlyData

(

CategoryID int IDENTITY(1,1)

,Categories varchar(30) NOT NULL

,Value varchar(50)

)

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-02', 'Small')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-02', 'Medium')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-02', 'Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-03', 'Extra Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-03', 'Small')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-03', 'Medium')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-04', 'Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-04', 'Extra Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-04', 'Small')

DECLARE @.string nvarchar(1000)

SELECT @.string = ISNULL(@.string + ', ', '') + QUOTENAME([Value], '''') + QUOTENAME(Categories)

FROM dbo.MonthlyData

SET @.string = 'SELECT ' + @.string

EXEC sp_executesql @.string

This will give you Categories as your column header with associated Value column.

|||

Thank you for your help, I will try this!

cdun2

|||

Thanks again. I had a couple of questions;

-Procedure sp_executesql expects parameter '@.statement' of type 'ntext/nchar/nvarchar'; nvarchar has a 'size' limit of 4000, and ntext cannot be the datatype of local variable @.string. char will handle up to 8000 characters. How can I work around these limitations?

-Could a table be created from the results of sp_executesql @.string so that the column names become fields in the table?

cdun2

|||

Yes, you could alter the syntax

@.sql = 'select ' + @.sql

to

@.sql = 'select ' + @.sql + ' into myTable'

this would keep the columns dynamic. If the number of columns will be static you could create the table and do the following:

@.sql = 'insert myTable (valuelist) select ' + @.sql

|||I realized I didn't answer the first part of your question. If the string you are trying to pass is greater than the 4000 limit of nvarchar you can replace sp_executesql with exec() and make @.string a varchar(8000). It is better practice to use sp_executesql with dynamic sql but in this case it is your only real option.