将多行转换为单行 [英] Convert a multiple row to a single row

问题描述

先生，

我有这种格式的表格。

Sir,
I have a table in this format.

class subject no_of_boys no_of_girls
1         A       40        60
1         B       50        70
1         C       80        50
2         A       55        40
2         B       57        65
2         C       54        29
3         A       42        14
3         B       68        53
3         C       40        10

需要此格式。

Need in this format.

CLASS A_BOYS A_GIRLS B_BOYS B_GIRLS C_BOYS C_GIRLS
1      40      60      50      70     80     50
2      55      40      57      65     54     29
3      42      14      68      53     40     10

请帮忙。

Please help.

推荐答案

试试这个：

Try this:

SELECT Class, A_BOYS, A_GIRLS, B_BOYS, B_GIRLS, C_BOYS, C_GIRLS
FROM (
    SELECT  Class, CONVERT(NVARCHAR(30), subject + '_Boys') Category, no_of_boys AS [Population]
    FROM Tbl_Class
    UNION ALL
    SELECT  Class, CONVERT(NVARCHAR(30), subject + '_Girls'), no_of_girls AS [Population]
    FROM Tbl_Class
    ) AS DT
PIVOT (SUM([Population]) FOR Category IN (A_BOYS, A_GIRLS, B_BOYS, B_GIRLS, C_BOYS, C_GIRLS)) AS PT

如果你想以动态方式进行，请点击以下链接：https://www.simple-talk.com/blogs/2007/09/14/ pivot-with-dynamic-columns-in-sql-server-2005 / [ ^ ]。

这篇关于将多行转换为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！