sql查询问题？涉及子查询、分组查询，求大神赐教，感激不尽……

Question

现有表store ，表中数据如下图所示：

如何通过sql语句 查出下面这种结果：

字段：id cname（药店） product_name(药品名) specification1(药品规格1) specification2(药品规格2)

请问应该如何写，求大神赐教，感激不尽……

创建数据库代码

CREATE TABLE [dbo].[store](

[id] [int] NOT NULL,
[cname] [varchar](50) NULL,
[product_name] [varchar](50) NULL,
[specification1] [varchar](50) NULL

) ON [PRIMARY]

INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (1, N'a药店', N'阿莫西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (2, N'a药店', N'阿莫西林', N'16片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'b药店', N'阿莫西林', N'6片装')

Answer 1

楼主这个需求我可以改一改，如果一个药品规格有N种的时候，你得到关于规格的列数就是N，N不确定，程序不好写。
我觉得从图一中得到的数据，做个遍历，就可以把规格合并成：

['a店'=>[
        '药品1'=>[
            '规格1',
            '规格2'，
            .......
            '规格N'
          ]
        ]
        '药品2'=>[....],
        .......
        '药品N'=>[....]

Answer 2

先看个效果：

;with a as(
select cname,product_name,specification1,N'specification规格'+convert(varchar,ROW_NUMBER()over(partition by cname,product_name order by specification1) ) as rn from store
)
select cname,product_name,specification规格1,specification规格2 from a pivot(max(a.specification1) for rn in (specification规格1,specification规格2)) b

cname product_name specification规格1 specification规格2
a药店 阿莫西林 16片装 6片装
b药店 阿莫西林 6片装 NULL

假设我理解的没错，你的数据可能还有其他药品，且规格不一定是两个，因此就要动态生成上面的语句：
比如上面的结果语句可以如下面生成。下面的不能正常是新是因为我这里用with，a表改为临时表即可

declare @sql nvarchar(max)
;with a as(
select cname,product_name,specification1,N'specification规格'+convert(varchar,ROW_NUMBER()over(partition by cname,product_name order by specification1) ) as rn from store
)

select  @sql= isnull(@sql+',','')+  rn from a group by rn
set @sql=N'select cname,product_name,'+@sql+N' from a'
              +N' pivot(max(a.specification1) for rn in ('+@sql+N')) b'
select @sql
exec（@sql）

如下我又价格几个产品：

CREATE TABLE [dbo].[store](

[id] [int] NOT NULL,
[cname] [varchar](50) NULL,
[product_name] [varchar](50) NULL,
[specification1] [varchar](50) NULL
) ON [PRIMARY]

INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (1, N'a药店', N'阿莫西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (2, N'a药店', N'阿莫西林', N'16片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'b药店', N'阿莫西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (2, N'a药店', N'阿莫西林西林', N'16片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'a药店', N'阿莫西林西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'a药店', N'阿莫西林西林', N'60片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'a药店', N'阿莫西林西林', N'600片装')
if OBJECT_ID('tempdb..#a') is not null drop table #a
declare @sql nvarchar(max)

select cname,product_name,specification1,N'specification规格'+convert(varchar,ROW_NUMBER()over(partition by cname,product_name order by specification1) ) as rn 
into #a from store


select  @sql= isnull(@sql+',','')+  rn from #a group by rn
set @sql=N'select cname,product_name,'+@sql+N' from #a'
              +N' pivot(max(specification1) for rn in ('+@sql+N')) b'
select @sql
exec(@sql)

结果如下：

|cname |product_name| specification规格1| specification规格2 |specification规格3 |specification规格4|
|a药店 |阿莫西林 |16片装 |6片装 |NULL |NULL|
|b药店 |阿莫西林 |6片装 |NULL |NULL |NULL|
|a药店 |阿莫西林西林 |16片装 |600片装 |60片装 |6片装|

Answer 3

典型的行转列

Answer 4

可以查詢出來再自己寫個函數改成想要的格式

Answer 5

这个需求用sql很难实现，因为本质上sql查询出的结果只能是个固定列数的二维表。
按照需求中的说法，同一种药品，如果规格不同，需要按列分别列出规格，这意味着列的个数是不固定。上面的样例数据中，阿莫西林有6片装和24片装两种规格，规格列是2列，但如果有12片装的呢，规格列是否应该是3列？

如果需求中所说的输出列，只是为了显示用，可以在程序（PHP/JAVA等）中取出所有数据，按照药店、药品名称作为唯一值放到一个集合中，然后循环输出对应的规格列。

Answer 6

上面这个貌似比较难实现，因为多出的列要根据数据分组来。
但是可以实现差不多的功能，没有分列，用逗号分隔。

select id, cname, product_name,(
    select group_concat(spec separator ',') from store s2
    where s1.cname = s2.cname    
) specification 
from store s1
group by cname