找到你要的答案

Q:Joining multiple rows based on a grouping factor

Q:基于分组因子加入多行

I'm trying to create a SQL query that will left join multiple rows together based on a grouping factor. Hopefully someone can help.

Example Data:

Code

001A

001B

001C

002A

002B

002C

002D

002E

I would want a query that does Where Code LIKE '001%' Then Left Join all the rows. Resulting in:

Code1   Code2   Code3

001A    001B    001C

In the data there would be variances in the number of codes that it would pick up for example on a Where Code Like '002%':

Code1   Code2   Code3   Code4   Code5

002A    002B    002C    002D    002E

Any help would be much appreciated.

我想创建一个SQL查询,将左连接多行一起根据分组因子。希望有人能帮助。

示例数据:

Code

001A

001B

001C

002A

002B

002C

002D

002E

I would want a query that does Where Code LIKE '001%' Then Left Join all the rows. Resulting in:

Code1   Code2   Code3

001A    001B    001C

在数据中,将有代码的数量的差异,它会拿起,例如在一个代码,如“% 002”:

Code1   Code2   Code3   Code4   Code5

002A    002B    002C    002D    002E

任何帮助将不胜感激。

answer1: 回答1:

You can do this with pivot and some dynamic query:

declare @cols varchar(max), @query varchar(max)

set @cols = stuff((select distinct ',' + quotename(code)
                   from t
                   where code like '002%'
                   for xml path(''), type).value('.', 'varchar(max)') 
                  ,1,1,'')

set @query = 'select * from (select code from t)t
              pivot (max(code) for code in (' + @cols + ')) p'
exec(@query)

Fiddle http://sqlfiddle.com/#!6/4da68/10

你可以用枢轴和一些动态查询:

declare @cols varchar(max), @query varchar(max)

set @cols = stuff((select distinct ',' + quotename(code)
                   from t
                   where code like '002%'
                   for xml path(''), type).value('.', 'varchar(max)') 
                  ,1,1,'')

set @query = 'select * from (select code from t)t
              pivot (max(code) for code in (' + @cols + ')) p'
exec(@query)

小提琴的HTTP:/ / sqlfiddle。COM / #!6 / 4da68 / 10

sql  sql-server