Script to generate a SQL table from a table structure (from the field names)

Question

Script to generate a SQL table from a table structure (from the field names)

GaMusicMan

Say Hey Kid

Points: 674
More actions
April 19, 2011 at 5:10 pm

#249194

I have a Customer table comprised of columns CustName, Addr1, Addr2, City, State , Zip, and YTDPurchases.
I am tring to create a table of metrics based on this Customer table that contains a Row for each Column Name The column names in the example below will contain metrics for the columns. I'm in affect flipping the table 90 degrees. I would like it to look like this example
7 columns from the Customer table becomes, 7 Rows in the new table.
Rows:
1) Name
2) Addr1
3) Addr2
4) City
5) State
6) Zip
7) YTDPurchases
If anyone has a script or suggestion as to how I might flip this Customer table structure 90 degrees so that I can populate the individual Column Name Rows with metrics. Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What are the metrics you are trying to include in those rows? Knowing the metrics or desired data affects the answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 2

I found the solution below. I do appreciate you taking the timne to read my post.

Here is the solution if anyone else has a similar project.

Use DHL_KEMP_Load

Declare @TableName Varchar(30) ;

Set @TableName = 'RAW_DHL_KEMP_CLAIM';

Select o.Name as 'Table', a.name as 'Field Name', SPACE(10) As '% Populated', SPACE(15) As 'Sum(Field Name)', SPACE(25) As 'Max Field Length',SPACE(15) As 'Greatest Value',

SPACE(15) As 'Min Value' from sys.all_objects o

inner join sys.all_columns a on o.object_id = a.object_id

where o.name = @TableName

No replies necessary... thank you.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

GaMusicMan (4/19/2011)
I found the solution below. I do appreciate you taking the timne to read my post.
Here is the solution if anyone else has a similar project.
Use DHL_KEMP_Load
Declare @TableName Varchar(30) ;
Set @TableName = 'RAW_DHL_KEMP_CLAIM';
Select o.Name as 'Table', a.name as 'Field Name', SPACE(10) As '% Populated', SPACE(15) As 'Sum(Field Name)', SPACE(25) As 'Max Field Length',SPACE(15) As 'Greatest Value',
SPACE(15) As 'Min Value' from sys.all_objects o
inner join sys.all_columns a on o.object_id = a.object_id
where o.name = @TableName
No replies necessary... thank you.

Considering that DOESN'T change the orientation of the data, how is this related to your original post?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff Moden (4/19/2011)
GaMusicMan (4/19/2011)
I found the solution below. I do appreciate you taking the timne to read my post.
Here is the solution if anyone else has a similar project.
Use DHL_KEMP_Load
Declare @TableName Varchar(30) ;
Set @TableName = 'RAW_DHL_KEMP_CLAIM';
Select o.Name as 'Table', a.name as 'Field Name', SPACE(10) As '% Populated', SPACE(15) As 'Sum(Field Name)', SPACE(25) As 'Max Field Length',SPACE(15) As 'Greatest Value',
SPACE(15) As 'Min Value' from sys.all_objects o
inner join sys.all_columns a on o.object_id = a.object_id
where o.name = @TableName
No replies necessary... thank you.
Considering that DOESN'T change the orientation of the data, how is this related to your original post?

As written, it also doesn't provide any statistical metrics on the data in each column either. If it is statistical data you seek, then we can whip up something for that too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events