如何在SQL Server中将行值显示为列值（仅一列行值应显示为多列） [英] How to display row value as column value in SQL Server (only one column rows value should be displayed as multiple columns)

问题描述

使用父表和子表的联接，我得到这样的结果

Using join of parent and child tables I am getting results like this

  select 
      a.id, a.pname, b.childname 
  from  
      parentinfo a 
  right join 
      childinfo b on a.id = b.pid

输出：

   id   pname       childname
   --------------------------
    1   Parent1     p1child1
    1   Parent1     p1child2
    1   Parent1     p1child3
    2   Parent2     p2child1
    2   Parent2     p2child2
    3   Parent2     p3child1
    3   Parent3     p3child2
    3   Parent3     p3child3
    3   Parent3     p3child4
    4   Parent4     p4child1
    4   Parent4     p4child2
    4   Parent4     p4child3

但是子项应显示为列，而一个父项应显示为列只显示一次。

But children should be displayed as columns, and one parent should be displayed only once.

而且可以有任意数量的孩子。

And there can be any numbers of children.

我想显示如下结果：

id  pname     child1    child2     child3     child4 
------------------------------------------------------
1   parent1   p1child1  p1child2   p1child3
2   parent2   p2child1  p2child2   
3   parent3   p3child1  p3child2   p3child3    p3child4
4   parent4   p4child1  p4child2   p4child3

如何实现呢？

此查询将所有行转换为列

This query converted all rows to columns

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

select @cols = STUFF((SELECT ',' + QUOTENAME(childname) 
                      FROM 
                          (SELECT * 
                           FROM parentinfo a 
                           RIGHT JOIN childinfo b ON a.id = b.pid) tt
                      GROUP BY childname, id
                      ORDER BY id
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

    set @query = 'SELECT ' + @cols + ' from 
         (
            select value, ColumnName
            from yourtable
        ) x
        pivot 
        (
            max(value)
            for ColumnName in (' + @cols + ')
        ) p '

     execute(@query)

推荐答案

这是使用动态交叉表的一种方法：

Here is one approach using dynamic crosstab:

SQL小提琴

生成示例数据

use tempdb;
CREATE TABLE yourtable(
    id          INT,
    pname       VARCHAR(20),
    childname   VARCHAR(20)
)
INSERT INTO yourtable VALUES
(1, 'Parent1', 'p1child1'), 
(1, 'Parent1', 'p1child2'), 
(1, 'Parent1', 'p1child3'), 
(2, 'Parent2', 'p2child1'), 
(2, 'Parent2', 'p2child2'), 
(3, 'Parent3', 'p3child1'), 
(3, 'Parent3', 'p3child2'), 
(3, 'Parent3', 'p3child3'), 
(3, 'Parent3', 'p3child4'), 
(4, 'Parent4', 'p4child1'), 
(4, 'Parent4', 'p4child2'), 
(4, 'Parent4', 'p4child3');

动态交叉表

DECLARE @maxNoChildren INT
DECLARE @sql1 VARCHAR(4000) = ''
DECLARE @sql2 VARCHAR(4000) = ''
DECLARE @sql3 VARCHAR(4000) = ''

SELECT TOP 1 @maxNoChildren = COUNT(*) FROM yourtable GROUP BY id ORDER BY COUNT(*) DESC

SELECT @sql1 = 
'SELECT
    id
    ,pname
'

SELECT @sql2 = @sql2 +
'   ,MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(5), N) + ' THEN childname END) AS ' + QUOTENAME('child' + CONVERT(VARCHAR(5), N)) + CHAR(10)
FROM(
    SELECT TOP(@maxNoChildren)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM sys.columns a
    --CROSS JOIN sys.columns b
)T(N)
ORDER BY N

SELECT @sql3 =
'FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL))
    FROM yourtable
)t
GROUP BY id, pname
ORDER BY id'

PRINT(@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

结果

| id |   pname |   child1 |   child2 |   child3 |   child4 |
|----|---------|----------|----------|----------|----------|
|  1 | Parent1 | p1child1 | p1child2 | p1child3 |   (null) |
|  2 | Parent2 | p2child1 | p2child2 |   (null) |   (null) |
|  3 | Parent3 | p3child1 | p3child2 | p3child3 | p3child4 |
|  4 | Parent4 | p4child1 | p4child2 | p4child3 |   (null) |

这篇关于如何在SQL Server中将行值显示为列值（仅一列行值应显示为多列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！