about tsql output - 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

about tsql output

Author

Topic

eirikr
Starting Member

18 Posts

Posted - 2008-11-24 : 16:52:11

Having 4 tables

System has columns: id, sysName
PC has has columns: id, serialNum
Mac has has columns: id, macAddr
IP has has columns: id, ipAddr

Example:

System Serial Mac IP

Sys1 Ser1 Mac1 IP1
Sys1 Ser1 Mac2 IP2

How can I make the output like

Sys1 Ser1 “Mac1; Mac2” “IP1; IP2”

Please help (using T_sql, MSSQL 2005)
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 16:59:24

A maximum of two concatenations?

DECLARE	@Sample TABLE
	(
		System CHAR(4),
		Serial CHAR(4),
		Mac CHAR(4),
		IP CHAR(3)
	)

INSERT	@Sample
SELECT	'Sys1', 'Ser1', 'Mac1', 'IP1' UNION ALL
SELECT	'Sys1', 'Ser1', 'Mac2', 'IP2'

SELECT	*
FROM	@Sample

SELECT		System,
		Serial,
		MIN(Mac) + COALESCE('; ' + NULLIF(MAX(Mac), MIN(Mac)), '') AS Mac,
		MIN(IP) + COALESCE('; ' + NULLIF(MAX(IP), MIN(IP)), '') AS IP
FROM		@Sample
GROUP BY	System,
		Serial
ORDER BY	System,
		Serial

E 12°55'05.63"
N 56°04'39.26"

eirikr
Starting Member

18 Posts

Posted - 2008-11-24 : 17:13:25

ty for reply, there is any another way without creating @sample table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 17:18:46

The first part is only for mimicing your environment.
This is what you want

SELECT		System,
		Serial,
		MIN(Mac) + COALESCE('; ' + NULLIF(MAX(Mac), MIN(Mac)), '') AS Mac,
		MIN(IP) + COALESCE('; ' + NULLIF(MAX(IP), MIN(IP)), '') AS IP
FROM		YourTableNameHere
GROUP BY	System,
		Serial
ORDER BY	System,
		Serial

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources