need to get 1 row/record

Question

Post reply

need to get 1 row/record

denis.gendera

Right there with Babe

Points: 733
More actions
January 12, 2011 at 9:33 am

#230033

Hi,
I've got following data that I need to return in unique row/machine
name mac_address
sn405153650:50:54:50:30:30
sn405153633:50:6F:45:30:30
sn405153600:1E:0B:2A:45:EF
sn405153600:FF:60:E5:F3:89
sn3432323 00:FF:61:E5:F3:89
sn3432323 00:FF:62:E5:F3:89
Result should be following
name mac_address1 mac_address2 mac_address3 mac_address4
sn4051536 50:50:54:50:30:30 33:50:6F:45:30:30 00:1E:0B:2A:45:EF 00:FF:60:E5:F3:89
sn3432323 00:FF:61:E5:F3:89 00:FF:62:E5:F3:89 NULL NULL
I've looked at dynamic PIVOT but was not so clear, so any example could be helpfull.
the source query contains of multiple joins etc, if necessary I can dump it into temp table
Thx for any help.

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

How's this?

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

DECLARE @test-2 TABLE (name varchar(15), mac_address varchar(17));

INSERT INTO @test-2

SELECT 'sn4051536', '50:50:54:50:30:30' UNION ALL

SELECT 'sn4051536', '33:50:6F:45:30:30' UNION ALL

SELECT 'sn4051536', '00:1E:0B:2A:45:EF' UNION ALL

SELECT 'sn4051536', '00:FF:60:E5:F3:89' UNION ALL

SELECT 'sn3432323', '00:FF:61:E5:F3:89' UNION ALL

SELECT 'sn3432323', '00:FF:62:E5:F3:89';

WITH CTE AS

(

SELECT name,

mac_address,

RN = ROW_NUMBER() OVER (PARTITION BY name ORDER BY (select 0))

FROM @test-2

)

SELECT name,

mac_address_1 = MAX(CASE WHEN RN = 1 THEN mac_address ELSE NULL END),

mac_address_2 = MAX(CASE WHEN RN = 2 THEN mac_address ELSE NULL END),

mac_address_3 = MAX(CASE WHEN RN = 3 THEN mac_address ELSE NULL END),

mac_address_4 = MAX(CASE WHEN RN = 4 THEN mac_address ELSE NULL END)

FROM CTE

GROUP BY name;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

denis.gendera Right there with Babe Points: 733 More actions · Answer 2

Hi,

Thx for the quick reply and this indeed solved my issue. Really appreciated. Take care.

anand_vanam SSCrazy Points: 2376 More actions · Answer 3

Just for curiosity..

if we don't know the maximum numbers of mac_address's associated for the Name.

Say here we have 4 mac_address associated with the Name "sn4051536", so went for RN=4 ... what If we have lot of records with the same Name and different Mac_address ??

Is there any generic way to get this with out writing this

" mac_address_1 = MAX(CASE WHEN RN = 1 THEN mac_address ELSE NULL END)" multiple times in the select statement.

Thanks.

AV

LutzM SSC Guru Points: 107049 More actions · Answer 4

anand_vanam (1/17/2011)
Just for curiosity..
if we don't know the maximum numbers of mac_address's associated for the Name.
Say here we have 4 mac_address associated with the Name "sn4051536", so went for RN=4 ... what If we have lot of records with the same Name and different Mac_address ??
Is there any generic way to get this with out writing this
" mac_address_1 = MAX(CASE WHEN RN = 1 THEN mac_address ELSE NULL END)" multiple times in the select statement.
Thanks.
AV

You could use the DynamicCrossTab method. A description can is linked in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]