SQL Server 2008 在不知道值的情况下进行透视

发布于 2024-10-31 00:02:37 字数 812 浏览 0 评论 0原文

在 SQL Server 2008 中，我有下表。

在此处输入图像描述

我不知道如何使用 Pivot 而不给出 ([val1],[val2],[ val2],..)

任何这方面的帮助都将不胜感激。

谢谢。

create table [VJ1].[dbo].[pivot] (class varchar(25) null, name varchar(25) null)

insert into [VJ1].[dbo].[pivot] (class,name) 
values ('class1','Peter'),
       ('class1','John'),
       ('class1','Marry'),
       ('class1','Ana'),
       ('class1','Julie'),
       ('class1','Lydia'),           
       ('class2','Ryan'),
       ('class2','Aaron'),           
       ('class2','Jacques'),
       ('class2','Jaanu'),           
       ('class3','Nita'),
       ('class3','Nina'),
       ('class3','Lili'),           
       ('class3','Rose'),
       ('class3','Jack'),
       ('class3','Tom')

原文

In SQL server 2008, I have below table.

enter image description here

I do not how to use Pivot without giving for value in ([val1],[val2],[val2],..)

Any hep in this regard will be greatly appreciated.

Thanks.

create table [VJ1].[dbo].[pivot] (class varchar(25) null, name varchar(25) null)

insert into [VJ1].[dbo].[pivot] (class,name) 
values ('class1','Peter'),
       ('class1','John'),
       ('class1','Marry'),
       ('class1','Ana'),
       ('class1','Julie'),
       ('class1','Lydia'),           
       ('class2','Ryan'),
       ('class2','Aaron'),           
       ('class2','Jacques'),
       ('class2','Jaanu'),           
       ('class3','Nita'),
       ('class3','Nina'),
       ('class3','Lili'),           
       ('class3','Rose'),
       ('class3','Jack'),
       ('class3','Tom')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吲‖鸣 2024-11-07 00:02:37

您不需要知道这些值，只需要知道与类关联的名称的最大数量。

;WITH PvtCte as
(
select  Class,Name,RANK() over (partition by Class order by Name) r
from    [pivot]
)
SELECT class,[1],[2],[3],[4],[5],[6]
FROM 
(SELECT Class,Name,r
FROM PvtCte) p
PIVOT
(
MIN(Name)
FOR r IN
( [1], [2], [3], [4], [5],[6] )
) AS pvt

如果您不知道与某个类关联的名称的最大数量，您可以尝试在上面构建相同的查询。希望有帮助！

You don´t need to know the values, just the maxium number of names associated with a class.

;WITH PvtCte as
(
select  Class,Name,RANK() over (partition by Class order by Name) r
from    [pivot]
)
SELECT class,[1],[2],[3],[4],[5],[6]
FROM 
(SELECT Class,Name,r
FROM PvtCte) p
PIVOT
(
MIN(Name)
FOR r IN
( [1], [2], [3], [4], [5],[6] )
) AS pvt

If you aldo don´t know the maxium number of names associated with a class you can try building the same query above dinamicaly. Hope it helps!

回复收藏 0 原文

~没有更多了~