Sql在同一ID下连接字符串属性

/****** Script for SelectTopNRows command from SSMS ******/

WITH IDS AS (

SELECT DISTINCT ID

FROM [test].[dbo].[metadata]

)

, AssetType AS (

SELECT DISTINCT ID,[Asset Type]

FROM [test].[dbo].[metadata]

)

, [Department] AS (

SELECT DISTINCT ID,[Department ]

FROM [test].[dbo].[metadata]

)

, [Product Category] AS (

SELECT DISTINCT ID,[Product Category]

FROM [test].[dbo].[metadata]

)

, Adjacency AS (

SELECT DISTINCT ID,[Adjacency]

FROM [test].[dbo].[metadata]

)

, [Selling Strategy] AS (

SELECT DISTINCT ID,[Selling Strategy]

FROM [test].[dbo].[metadata]

)

, [Sales Organization] AS (

SELECT DISTINCT ID,[Sales Organization]

FROM [test].[dbo].[metadata]

)

 

SELECT CO.ID

, COALESCE(

STUFF(

(SELECT ' , ' + CAST([Asset Type] AS VARCHAR(MAX))

FROM AssetType C

WHERE C.id = CO.ID

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),

1, 3, ''

)

, '') AS AssetType

, COALESCE(

STUFF(

(SELECT ' , ' + CAST([Department] AS VARCHAR(MAX))

FROM Department C

WHERE C.id = CO.ID

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),

1, 3, ''

)

, '') AS Department

, COALESCE(

STUFF(

(SELECT ' , ' + CAST([Product Category] AS VARCHAR(MAX))

FROM [Product Category] C

WHERE C.id = CO.ID

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),

1, 3, ''

)

, '') AS [Product Category]

FROM IDS CO