Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best way to concatenate multiple rows in multiple columns Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 1:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:15 PM
Points: 18, Visits: 282
Hi,

I have a question. I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [Person].[Person_1] ([BusinessEntityID],
[PersonType],
[FirstName])

SELECT 1, 'EM', 'test1'
UNION ALL
SELECT 2, 'EM', 'test2'
UNION ALL
SELECT 3, 'EM', 'test3'
UNION ALL
SELECT 4, 'SC', 'test4'
UNION ALL
SELECT 5, 'SC', 'test5'
UNION ALL
SELECT 6, 'SC', 'test6'


--Concatenate multiple rows into two columns
SELECT STUFF((SELECT ' ' + [FirstName]
FROM [AdventureWorks2012].[Person].[Person_1] pers

WHERE pers.[PersonType] = 'EM'

ORDER BY pers.[BusinessEntityID] ASC
FOR XML PATH(''), type).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')

,STUFF((SELECT ' ' + [FirstName]
FROM [AdventureWorks2012].[Person].[Person_1] pers

WHERE pers.[PersonType] = 'SC'

ORDER BY pers.[BusinessEntityID] ASC
FOR XML PATH(''), type).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
,pp.[PersonType]


FROM [AdventureWorks2012].[Person].[Person_1] pp

WHERE pp.[BusinessEntityID] =1


This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?




Best regards
Post #1599910
Posted Tuesday, August 5, 2014 2:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 2,192, Visits: 5,929
Not much I would suggest here apart from the missing separator in the concatenation and a covering index. Otherwise this would be pretty much my first and probably only approach. Of course it all depends on the size of the set, cardinality etc. but one has to accept the expense of such an operation and from that pint, your query looks good.
Post #1599927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse