如何在sql server 2005/8中没有聚合函数的情况下进行列到行？

发布于 2024-09-29 05:42:21 字数 798 浏览 3 评论 0原文

例如，我需要从

alt text

更改为

替代文本 .

我知道 PIVOT 就是用于此目的，但它需要一个聚合函数；对于我的情况，我不需要聚合，只需要列到行。

您可以使用以下示例数据：

    CREATE TABLE[StudentScores] 
( 
[UserName] NVARCHAR(20),
[Subject] NVARCHAR(30),
[Score]FLOAT,
) 
GO

INSERT INTO[StudentScores]SELECT'Nick','Chinese',80 

INSERT INTO[StudentScores]SELECT'Nick','Maths',90 

INSERT INTO[StudentScores]SELECT'Nick','English',70 

INSERT INTO[StudentScores]SELECT'Nick','Biology',85 

INSERT INTO[StudentScores]SELECT'Kent','Chinese',80 

INSERT INTO[StudentScores]SELECT'Kent','Maths',90 

INSERT INTO[StudentScores]SELECT'Kent','English',70 

INSERT INTO[StudentScores]SELECT'Kent','Biology',85

原文

For example, I need to change from

alt text

alt text .

I know PIVOT is for that, but it requires an aggregate function; and for my case, I donot need to aggregate only need column to row.

You can use the following sample data:

    CREATE TABLE[StudentScores] 
( 
[UserName] NVARCHAR(20),
[Subject] NVARCHAR(30),
[Score]FLOAT,
) 
GO

INSERT INTO[StudentScores]SELECT'Nick','Chinese',80 

INSERT INTO[StudentScores]SELECT'Nick','Maths',90 

INSERT INTO[StudentScores]SELECT'Nick','English',70 

INSERT INTO[StudentScores]SELECT'Nick','Biology',85 

INSERT INTO[StudentScores]SELECT'Kent','Chinese',80 

INSERT INTO[StudentScores]SELECT'Kent','Maths',90 

INSERT INTO[StudentScores]SELECT'Kent','English',70 

INSERT INTO[StudentScores]SELECT'Kent','Biology',85

分享到QQ

分享到微博

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

发布评论

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

还如梦归 2024-10-06 05:42:21

如果每个主题只有一条记录，您可以使用 MIN 或 MAX。

SELECT *
FROM [StudentScores]
PIVOT
(
  MIN(Score)
  FOR [Subject] IN ([Chinese],[Maths],[English],[Biology])
)
AS p

If there is going to be one record per subject you can use MIN or MAX.

SELECT *
FROM [StudentScores]
PIVOT
(
  MIN(Score)
  FOR [Subject] IN ([Chinese],[Maths],[English],[Biology])
)
AS p

回复收藏 0 原文

￡烟消云散 2024-10-06 05:42:21

我无法从你原来的问题中完全看出你想改变哪个领域——科目还是分数。但是，您可以使用 PIVOT< /code>来执行此操作。如果您知道要从行更改为列的列数，则可以使用静态数据透视（类似于其他答案）。如果您不知道要转换的列数，则可以使用动态数据透视：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(subject) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT username,' + @cols + ' from 
         (
            select username, subject, score
            from test
         ) x
         pivot 
         (
            avg(score)
            for subject in(' + @cols + ')
         ) p '

execute(@query)

请参阅 SQL Fiddle在演示中

，如果用户每个科目有多个分数，我会使用 AVG() 聚合。

I cannot quite tell from your original question which field you want to transform - subject or score. However, you can use a PIVOT to perform this. If you know the number of columns you want to change from rows to columns, then you can use a static pivot (similar to the other answer). If you do not know the number of columns to transform, then you can use a dynamic pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(subject) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT username,' + @cols + ' from 
         (
            select username, subject, score
            from test
         ) x
         pivot 
         (
            avg(score)
            for subject in(' + @cols + ')
         ) p '

execute(@query)

See SQL Fiddle with Demo

I used the AVG() aggregate in the event a user has more than one score per subject.

回复收藏 0 原文