Turn data output 90 degress

Question

Turn data output 90 degress

Ib Larsen

Grasshopper

Points: 24
More actions
July 2, 2006 at 4:03 am

#94473

Hi
Can anybody help me with this problem:
I have a table on a SQL2005 server who look like this:
(Columnnames)
[Nr] [NameType] [Value]
(Datra)
1 Firstname Anders
2 Firstname Bent
3 Firstname Claus
4 Firstname Dennis
2 Lastname Børgesen
3 Lastname Clausen
I would like to get it converted to the following format
(Columnnames)
[Nr] [Firstname] [Lastname]
(Data)
1 Anders
2 Bent Børgesen
3 Claus Clausen
4 Dennis
I can do it by using some clientcode to generate the wanted format. But what I want is - to let the
SQL2005 server do it. I have tried using "PIVOT" on the SQL2005 server. But without any luck.
Is there anybody who can help me?
Regards Ib

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

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

PIVOT works best when you are using aggregations (count(), sum() avg(), etc.). In this case, because you want to return every value instead of the results of an aggregation over them, a simple join does the trick:

DECLARE

@testr table(Nr int NOT NULL, NameType varchar(25) NOT NULL, NameValue varchar(25) NOT NULL)

INSERT @testr(Nr, NameType, NameValue)

SELECT 1, 'Firstname', 'Anders' UNION

SELECT 2, 'Firstname', 'Bent' UNION

SELECT 3, 'Firstname', 'Claus' UNION

SELECT 4, 'Firstname', 'Dennis' UNION

SELECT 2, 'Lastname', 'Børgesen' UNION

SELECT 3, 'Lastname', 'Clausen'

-- Join the two subsets of the source data (the list of Firstname values and the list of Lastname values)

-- using the Nr value. The FULL OUTER JOIN allows either the first name or last name to be missing, and the

-- opposite value will still return from the query:

SELECT

COALESCE(fn.Nr, ln.Nr) AS [Nr], IsNull(fn.NameValue, '') AS [FirstName], IsNull(ln.NameValue, '') AS [LastName]

FROM @testr fn FULL OUTER JOIN

@testr ln ON fn.Nr = ln.Nr AND ln.NameType = 'Lastname'

WHERE fn.NameType = 'Firstname'

Nr FirstName LastName

1 Anders

2 Bent Børgesen

3 Claus Clausen

4 Dennis

-Eddie

Eddie Wuerch
MCM: SQL

Ib Larsen Grasshopper Points: 24 More actions · Answer 2

Hi Eddie

That was exactly what I was looking for.

Thank you

Ib

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Here's an alternative. Ib - If speed is important to you, you might find it performs a bit quicker, but you'll have to test that yourself.

select Nr,

max(case when NameType = 'Firstname' then NameValue else '' end) AS [FirstName],

max(case when NameType = 'Lastname' then NameValue else '' end) AS [LastName]

from @testr group by Nr

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.