Transposing SQL Tables - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Transposing SQL Tables

Author

Topic

shortlyday
Starting Member

6 Posts

Posted - 2008-03-12 : 09:43:25

hi there, i've got a Maconomy table that needs to be normalised. I'm fairly new to T-SQL and was hoping i could get advice on this forum. Possibly copy the below tables into Excel to make better sense.

i would like to PiVot/transpose the below table to so that the 8 types of HEADER are the columns but include the NOTENUMBER (i.e. this below table would give an output of 2 rows)

any takers?

NOTENUMBER HEADER AMOUNT1 STRING1 DATE1
1100030 Insurance cost 44
1100030 Leasing cost 300
1100030 PropertyID 0 N3100023424
1100030 PropertyModel 0 Nokia 3100
1100030 PropertyName 0 Cell Phone
1100030 PropertyYear 0 2003.01.11
1100030 Scheduled end date 0 2004.12.20
1100030 Tax cost 463
1100031 Insurance cost 50
1100031 Leasing cost 400
1100031 PropertyID 0 N3100023424
1100031 PropertyModel 0 Siemens
1100031 PropertyName 0 Cell Phone
1100031 PropertyYear 0 2003.01.11
1100031 Scheduled end date 0 2004.12.20
1100031 Tax cost 500

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 09:54:55

Read about the PIVOT operator in SQL Server 2005 help.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 10:09:21

Or have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98885

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 10:46:12

No. Pivot makes you have all columns the same datatype.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:03:21

See the link I provided earlier.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98885

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:24:01

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
NoteNumber INT,
Header VARCHAR(20),
Amount INT,
String VARCHAR(20),
Date DATETIME
)

INSERT @Sample
SELECT 1100030, 'Insurance cost', 44, NULL, NULL UNION ALL
SELECT 1100030, 'Leasing cost', 300, NULL, NULL UNION ALL
SELECT 1100030, 'PropertyID', 0, 'N3100023424', NULL UNION ALL
SELECT 1100030, 'PropertyModel', 0, 'Nokia 3100', NULL UNION ALL
SELECT 1100030, 'PropertyName', 0, 'Cell Phone', NULL UNION ALL
SELECT 1100030, 'PropertyYear', 0, NULL, '2003.01.11' UNION ALL
SELECT 1100030, 'Scheduled end date', 0, NULL, '2004.12.20' UNION ALL
SELECT 1100030, 'Tax cost', 463, NULL, NULL UNION ALL
SELECT 1100031, 'Insurance cost', 50, NULL, NULL UNION ALL
SELECT 1100031, 'Leasing cost', 400, NULL, NULL UNION ALL
SELECT 1100031, 'PropertyID', 0, 'N3100023424', NULL UNION ALL
SELECT 1100031, 'PropertyModel', 0, 'Siemens', NULL UNION ALL
SELECT 1100031, 'PropertyName', 0, 'Cell Phone', NULL UNION ALL
SELECT 1100031, 'PropertyYear', 0, NULL, '2003.01.11' UNION ALL
SELECT 1100031, 'Scheduled end date', 0, NULL, '2004.12.20' UNION ALL
SELECT 1100031, 'Tax cost', 500, NULL, NULL

-- Solution
SELECT NoteNumber,
MAX(CASE WHEN Header = 'Insurance cost' THEN Amount ELSE NULL END) AS [Insurance cost],
MAX(CASE WHEN Header = 'Leasing cost' THEN Amount ELSE NULL END) AS [Leasing cost],
MAX(CASE WHEN Header = 'PropertyID' THEN String ELSE NULL END) AS PropertyID,
MAX(CASE WHEN Header = 'PropertyModel' THEN String ELSE NULL END) AS PropertyModel,
MAX(CASE WHEN Header = 'PropertyName' THEN String ELSE NULL END) AS PropertyName,
MAX(CASE WHEN Header = 'PropertyYear' THEN Date ELSE NULL END) AS PropertyYear,
MAX(CASE WHEN Header = 'Scheduled end date' THEN Date ELSE NULL END) AS [Scheduled end date],
MAX(CASE WHEN Header = 'Tax cost' THEN Amount ELSE NULL END) AS [Tax cost]
FROM @Sample
GROUP BY NoteNumber
ORDER BY NoteNumber[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:42:14

And using PIVOT, notice that all colums are of same datatype

SELECT		p.[Insurance cost],
		p.[Leasing cost],
		p.[PropertyID],
		p.[PropertyModel],
		p.[PropertyName],
		p.[PropertyYear],
		p.[Scheduled end date],
		p.[Tax cost]
FROM		(
			SELECT	NoteNumber,
				Header,
				COALESCE(String, CONVERT(VARCHAR(21), Date, 120), CAST(Amount AS VARCHAR(20))) AS theData
			FROM	@Sample
		) AS s
PIVOT		(
			MAX(s.theData)
			FOR s.Header IN ([Insurance cost], [Leasing cost], [PropertyID], [PropertyModel], [PropertyName], [PropertyYear], [Scheduled end date], [Tax cost])
		) AS p

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

shortlyday
Starting Member

6 Posts

Posted - 2008-03-13 : 02:24:38

hi Peso - quick question:

why does the script HAVE to have the 'MAX' function?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 03:31:33

Because transposing row to columns is an aggregation operation.
Read about PIVOT / CROSS TAB in Books Online.

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

shortlyday
Starting Member

6 Posts

Posted - 2008-03-13 : 03:47:49

cool thanks Peso. Will the script carry over the Data Types as well?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 03:58:27

Yes.

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

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-20 : 01:49:43

Hi,

Try this

create TABLE #Sample
( NoteNumber INT,
Header VARCHAR(20),
Amount INT,
String VARCHAR(20),
Date DATETIME
)
INSERT #Sample
SELECT 1100030, 'Insurance cost', 44, NULL, NULL UNION ALL
SELECT 1100030, 'Leasing cost', 300, NULL, NULL UNION ALL
SELECT 1100030, 'PropertyID', 0, 'N3100023424', NULL UNION ALL
SELECT 1100030, 'PropertyModel', 0, 'Nokia 3100', NULL UNION ALL
SELECT 1100030, 'PropertyName', 0, 'Cell Phone', NULL UNION ALL
SELECT 1100030, 'PropertyYear', 0, NULL, '2003.01.11' UNION ALL
SELECT 1100030, 'Scheduled end date', 0, NULL, '2004.12.20' UNION ALL
SELECT 1100030, 'Tax cost', 463, NULL, NULL UNION ALL
SELECT 1100031, 'Insurance cost', 50, NULL, NULL UNION ALL
SELECT 1100031, 'Leasing cost', 400, NULL, NULL UNION ALL
SELECT 1100031, 'PropertyID', 0, 'N3100023424', NULL UNION ALL
SELECT 1100031, 'PropertyModel', 0, 'Siemens', NULL UNION ALL
SELECT 1100031, 'PropertyName', 0, 'Cell Phone', NULL UNION ALL
SELECT 1100031, 'PropertyYear', 0, NULL, '2003.01.11' UNION ALL
SELECT 1100031, 'Scheduled end date', 0, NULL, '2004.12.20' UNION ALL
SELECT 1100031, 'Tax cost', 500, NULL, NULL

--Select * From #Sample

Declare @Str Varchar(Max), @Sql Varchar(Max)
Select @Str = '', @Sql = ''

Select @Sql = @sql + ', Min(Case when CAST(Header as varchar(1000)) = ''' + Header + ''' then Amount End ) As "'+ Header +'"'
--,Min(Case when CAST(Header as varchar(1000)) = ''' + Header + ''' then String End ) As "'+ Header +'"
--,Min(Case when CAST(Header as varchar(1000)) = ''' + Header + ''' then Date End ) As "'+ Header +'"'
From (Select Distinct Header From #Sample)A

Select @Str = 'Select NoteNumber '+@Sql+' FRom #Sample group By NoteNumber'
Print @Str
Exec (@str)

Drop Table #SAmple

Kift
Starting Member

14 Posts

Posted - 2008-07-22 : 05:36:09

I have a questions regarding this as it seems to answer perfectly what it is I'm trying to do.

The question I have refers to data types. The ones I'm using are NVARCHAR which assume will be String, BIGINT which I assume is Amount and Real. I'm not sure if Real counts as Amount when setting this query up.

I'm sure this is a really stupid question to be asking, but I'm still new to all to this so I find its best to ask those who know.

Thanks.

Kift.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 05:44:28

quote:
Originally posted by Kift

I have a questions regarding this as it seems to answer perfectly what it is I'm trying to do.

The question I have refers to data types. The ones I'm using are NVARCHAR which assume will be String, BIGINT which I assume is Amount and Real. I'm not sure if Real counts as Amount when setting this query up.

I'm sure this is a really stupid question to be asking, but I'm still new to all to this so I find its best to ask those who know.

Thanks.

Kift.

didnt get what you're asking. can you make it clear please?

Kift
Starting Member

14 Posts

Posted - 2008-07-22 : 05:51:04

I'm trying to use the code that Peso wrote above.

In it, for every variable he/she has defined where its VARCHAR datatype its String, where its DATETIME datatype it is Date, where its an INT datatype its Amount. My question, is where its a REAL datatype what do I define it as? Is it String, Amount, Date or is there another one such as Real? I'm not sure, so I'm hoping you can help me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources