converting nrows into columns - SQL Server Forums

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

converting nrows into columns

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-09-25 : 11:58:21

Hi,

I need to flip around the way data is presented in a table.
I've got columns called 'datafield', and 'datavalue' in one table and I want to move the datavalue into a second table where the column name is the same as the datafield.

Below is the sql to create the first table and populate it, I've also included the SQL to create the second table. I want to move the data from teststart to testfinish. There may also be columns in test finish that aren't included in the data of teststart.

create TABLE teststart
(
ticker varchar(255) NOT NULL,
datafield varchar(255) NOT NULL,
datavalue varchar(255) NOT NULL
)

INSERT INTO teststart
select 'ZTX LN Equity', 'EXCH_CODE', 'LN' union
select 'ZTX LN Equity', 'ID_BB_COMPANY', '112367' union
select 'ZTX LN Equity', 'ID_BB_SECURITY', '1000' union
select 'ZTX LN Equity', 'ID_ISIN', 'GB0008812496' union
select 'ZTX LN Equity', 'ID_MIC_LOCAL_EXCH', 'XLON' union
select 'ZTX LN Equity', 'ID_SEDOL1', '0881249'

CREATE TABLE testfinish
(
ticker varchar(255) NOT NULL,
EXCH_CODE varchar(255),
ID_BB_COMPANY varchar(255),
ID_BB_SECURITY varchar(255),
ID_ISIN varchar(255),
ID_MIC_LOCAL_EXCH varchar(255),
ID_SEDOL1 varchar(255),
Another varchar(255)
)

I'd be grateful for any pointers, thanks.

Sean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 12:10:25

Why do you want to denormalize your table structure?

DECLARE	@Sample TABLE (Ticker VARCHAR(255), DataField VARCHAR(255), DataValue VARCHAR(255))

INSERT	@Sample
SELECT	'ZTX LN Equity', 'EXCH_CODE', 'LN' UNION
SELECT	'ZTX LN Equity', 'ID_BB_COMPANY', '112367' UNION
SELECT	'ZTX LN Equity', 'ID_BB_SECURITY', '1000' UNION
SELECT	'ZTX LN Equity', 'ID_ISIN', 'GB0008812496' UNION
SELECT	'ZTX LN Equity', 'ID_MIC_LOCAL_EXCH', 'XLON' UNION
SELECT	'ZTX LN Equity', 'ID_SEDOL1', '0881249'

SELECT		Ticker,
		MAX(CASE WHEN DataField = 'EXCH_CODE' THEN DataValue END) AS EXCH_CODE,
		MAX(CASE WHEN DataField = 'ID_BB_COMPANY' THEN DataValue END) AS ID_BB_COMPANY,
		MAX(CASE WHEN DataField = 'ID_BB_SECURITY' THEN DataValue END) AS ID_BB_SECURITY,
		MAX(CASE WHEN DataField = 'ID_ISIN' THEN DataValue END) AS ID_ISIN,
		MAX(CASE WHEN DataField = 'ID_MIC_LOCAL_EXCH' THEN DataValue END) AS ID_MIC_LOCAL_EXCH,
		MAX(CASE WHEN DataField = 'ID_SEDOL1' THEN DataValue END) AS ID_SEDOL1,
		MAX(CASE WHEN DataField NOT IN ('EXCH_CODE', 'ID_BB_COMPANY', 'ID_BB_SECURITY', 'ID_ISIN', 'ID_MIC_LOCAL_EXCH', 'ID_SEDOL1') THEN DataValue END) AS Another
FROM		@Sample
GROUP BY	Ticker
ORDER BY	Ticker

E 12°55'05.25"
N 56°04'39.16"

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-09-26 : 03:12:56

Thanks for the reply, much appreciated.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources