Moving data form a Horizontal table to a Vertical

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Moving data form a Horizontal table to a Vertical

Author

Topic

westdh
Starting Member

1 Post

Posted - 2011-07-09 : 19:59:48

This script compiles OK, But at the end of the script the Horizontal table results are store in EXEC (@vSQL) How do I direct the var to my vertical tables CustomFieldValue]

SELECT [IssueId]
,[CustomFieldId]
,[CustomFieldValue]
FROM [storytrackerdnn].[dbo].[TE_IssueXP_Pr

-----------------------------------------------------------------
DECLARE @vSQL as VARCHAR(6000), @vCode As VARCHAR(6000)
Select @vCode = [CompanyName]
+ ',' +
+ ',' + [Cnxl]
+ ',' + [Research]
+ ',' + [Business_Catagory]
+ ',' + [ContactLastName]
+ ',' + [ContactFirstName]
+ ',' + [Title]
+ ',' + [Alt_Contact]
+ ',' + [WorkPhone]
+ ',' + [WorkExtension]
+ ',' + [HomePhone]
+ ',' + [FaxNumber]
+ ',' + [EmailName]
+ ',' + [BillingAddress]
+ ',' + [City]
+ ',' + [State]
+ ',' + [PostalCode]
+ ',' + [Published_Address]
+ ',' + [Published_City]
+ ',' + [Published_State]
+ ',' + [Published_PostalCode]
+ ',' + [Published_Phone]
+ ',' + [Published_website]
+ ',' + [Page_size]
+ ',' + [Freebie]
+ ',' + [Quantity]
+ ',' + [UnitPrice]
+ ',' + [Discount]
+ ',' + [SalePrice]
+ ',' + [SalesTax]
+ ',' + [LineTotal]
+ ',' + [Contract_signed]
+ ',' + [Paid]
+ ',' + [PaymentMethod]
+ ',' + [CheckNumber]
+ ',' + [CreditCardType]
+ ',' + [CreditCardNumber]
+ ',' + [CardholdersName]
+ ',' + [CreditCardExpDate]
+ ',' + [CreditCardAuthorizationNumber]
+ ',' + [Billing_notes]
+ ',' + [Date_of_Entry]
+ ',' + [Story_to_GW]
+ ',' + [Back_from_GW]
+ ',' + [Proof_to_client]
+ ',' + [Proof_from_client]
+ ',' + [Edited]
+ ',' + [nd_Proof_to_client]
+ ',' + [nd_Proof_from_client]
+ ',' + [To_GA]
+ ',' + [From_GA]
+ ',' + [Photo_rec]
+ ',' + [RFP]
+ ',' + [Photo_notes]
+ ',' + [Operation_notes] FROM InputTable where CustomerId ='47'
Select @vSQL = 'SELECT ''' + REPLACE(@vCode, ',', ''' UNION ALL SELECT ''') + ''''
EXEC (@vSQL)

---------------------------------------------------
or do you have a better way to convert Horizontal Table to a vertial Table. I only need to move one row at atime

Dennis West

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 08:04:02

Perhaps you may be able to shorten the query a little bit by using INFORMATION_SCHEMA views. Other than that, I have no suggestions as to how to do it better. However, I have couple of comments about it.

First, when you make the table "vertical", you are mixing all kinds of data types in your vertical table. What would be the data type of that final output column? Perhaps varchar? Know what it you want it to be and cast it appropriately to avoid surprises.

My second commend is based the use of dynamic SQL and the potential for security risks via SQL injection. While I don't see any SQL injection risks in your code, I am not a hacker, so I wouldn't count on my opinion on that. Just be aware that there is that risk.

Third, if the purpose of doing this is to provide the data to a client such as a presentation layer, if you are able to do this pivoting in the client, that may be a better approach.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-10 : 09:32:22

Firstly, pay attention to Sunita's response above, she provides some great advice.

Now, if you would still like to do it in SQL, here is an example of how to do something like this:

use pubs;

declare @sql nvarchar(max)

create table ##custom
(
attribute_id nvarchar(50),
attribute_value nvarchar(500)
)

set @sql = ''
select @sql = @sql + 'insert into ##custom select ''' + cast(name as nvarchar(1000)) + ''', ' + cast(name as nvarchar(1000)) + ' from authors where au_id = ''172-32-1176'';'
from sys.columns c where c.object_id = object_id('authors')

exec(@sql)
select * from ##custom

drop table ##custom

OS

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-10 : 15:47:37

Look up the design flaw known as "EAV"; read the tens of thousands of wo4rd written about why it is stinking awful bad.

You don't even know that fields are not columns, or any of the fundamentals of RDBMS. You screwed up the data element names. Even if you put that list of attributes into a table, you would have a useless de-normalized.

Throw this mess out and write an apology to Dr. Codd.

Seriously, it is that bad; stop.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources