sql mssql столбец в одну строку

sql-mssql-Столбец в одну строку

уникальны для каждой строки
[code:]sql[:code]with basicTable as 
	(
		select 
			 t0.a
			,t0.b
			,ROW_NUMBER() over (partition by t0.a order by t0.a) as rn
		from
		(
			select 1 as a, 'мама' as b
			union all
			select 1 as a, 'папа' as b
			union all
			select 1 as a, 'я' as b
		) t0
	)

	SELECT 
		t0.a
		,substring
		(
			(
				SELECT 
					'; ' + t1.b
				FROM
				(
					select 
						 t1.a
						,t1.b
					from basicTable t1
					where
						t0.a = t1.a
				) t1
				FOR XML
				PATH ('')
			), 2, 8000
		) as b
	FROM
	(
		select 
			 t0.a
			,t0.b
		from basicTable t0
	) t0
	GROUP BY
	  t0.a
[/code]
<pre>
1	 мама; папа; я
</pre>

уникальны по группам
[code:]LANG[:code]
with basicTable as 
	(
		select 
			 t0.b
			,t0.f
			,t0.income
			,DENSE_RANK() over (order by t0.f) as rn
		from
		(
			select 'Марина' as b, 'семья' as f, 12 as income
			union all
			select 'Денис' as b, 'семья' as f, 12 as income
			union all
			select 'Дарий' as b, 'семья' as f, 5 as income
			union all
			select 'Люба' as b, 'другая семья' as f, 12 as income
			union all
			select 'Стас' as b, 'другая семья' as f, 12 as income
			union all
			select 'Яна' as b, 'другая семья' as f, 5 as income
		) t0
	)

	SELECT 
		substring
		(
			(
				SELECT 
					'; ' + t1.b
				FROM
				(
					select 
						 t1.rn
						,t1.b
					from basicTable t1
					where
						t0.rn = t1.rn
				) t1
				FOR XML
				PATH ('')
			), 2, 8000
		) as b
		,t0.f
		,sum(t0.income) as income
	FROM
	(
		select 
			 t0.b
			,t0.f
			,t0.income
			,t0.rn
		from basicTable t0
	) t0
	GROUP BY
		 t0.rn

		,t0.f
[/code]
<pre>
 Стас; Люба; Яна	другая семья	29
 Дарий; Денис; Марина	семья	29
</pre>