MSSQL动态透视列值列为列标题 [英] MSSQL dynamic pivot column values to column header

问题描述

  ID | PROPERTY_NAME | PROPERTY_VALUE 
 1 | name1 |值
 1 | name2 |值
 1 | name3 |值
 2 | name4 |值
 2 | name2 |价值
 3 | name6 |价值
 .. 
  

PROPERTY_NAME& PROPERTY_VALUE是标题，ID有多个属性

我想将其转换为：

  ID | NAME1 | NAME2 | NAME3 | NAME4 | NAME5 | NAME6 | nameETC ... 
 1 |值|值|值| | | | valueETC ... 
 2 | |值| |值| | | valueETC ... 
 3 | | | | | |值| valueETC ... 
 .. 
  

其中NAME1 | NAME2 | NAME3等现在是列标题。

我猜想一个支点和从MycoolTable中选择不同的PROPERTY_NAME，但似乎不能把它们放在一起。 / p>

这是我所得到的：（没有server_id）列，所有的空值无处不在）显然我很愚蠢：D

  DECLARE @cols AS NVARCHAR（MAX），@query AS NVARCHAR（MAX）; 
 
选择@cols = STUFF（（SELECT distinct 
'，'+ QUOTENAME（PROPERTY_NAME）
 FROM [BSARA_DW_DB]。[dbo]。[SERVER_PROPERTY] 
 FOR XML PATH（''），TYPE 
）.value（'。'，'NVARCHAR（MAX）'）
，1,1，''）; 
 
 SET @query ='SELECT'+ @cols +'from 
（
 SELECT SERVER_ID，PROPERTY_NAME，PROPERTY_CHAR_VAL 
 FROM [BSARA_DW_DB]。[dbo] $'
 
 
（
 MAX（SERVER_ID）
（'+ @cols +'）中的PROPERTY_CHAR_VAL 
）p'; 
 
执行（@query）
  

非常感谢，
Mike

解决方案

您当前查询的问题与以下行：

  MAX（SERVER_ID）
  

你想显示 PROPERTY_CHAR_VAL 为每个 PROPERTY_NAME 。 SERVER_ID 将作为列的最终结果的一部分。

有时当您使用PIVOT时更容易编写代码，硬编码类似于：

  select id，name1，name2，name3，name4 
 
（
 select id，property_name，property_value 
 from yourtable 
）d 
 pivot 
（
 max（property_value） （name1，name2，name3，name4）中的property_name 
 
）piv; 
  

请参阅 SQL Fiddle with Demo 。

一旦你有一个具有正确逻辑的版本，那么你可以将其转换为动态SQL得到结果。这将创建一个将被执行的sql字符串，它将包括所有的新列名。

  DECLARE @cols AS NVARCHAR （MAX），
 @query AS NVARCHAR（MAX）
 
 select @cols = STUFF（（SELECT distinct'，'+ QUOTENAME（PROPERTY_NAME）
 from yourtable 
 FOR XML PATH（''），TYPE 
）.value（'。'，'NVARCHAR（MAX）'）
，1,1，''）
 
 set @从
（
 select id，property_name，property_value 
 from your table 
）x 
 pivot $查询='SELECT id''
 （'+ @cols +'）
（
 max（property_value）
 
）p'
 
执行sp_executesql @query; 
  

请参阅 SQL Fiddle with Demo 。两者都会得出结果：

  ID | NAME1 | NAME2 | NAME3 | NAME4 | NAME6 | 
 | ---- | -------- | -------- | -------- | -------- | ---- ---- | 
 | 1 |值|值|值| （null）| （null）| 
 | 2 | （null）|值| （null）|值| （null）| 
 | 3 | （null）| （null）| （null）| （null）|值| 
  

Could someone help me convert the following on mssql please?

 ID   |  PROPERTY_NAME  | PROPERTY_VALUE
 1    |      name1      |     value
 1    |      name2      |     value
 1    |      name3      |     value
 2    |      name4      |     value
 2    |      name2      |     value
 3    |      name6      |     value
..

PROPERTY_NAME & PROPERTY_VALUE being the headers, id having multiple 'properties'

I want to convert it to:

ID  |  NAME1  | NAME2  | NAME3  | NAME4  | NAME5  | NAME6  | nameETC...
1   |  value  | value  | value  |        |        |        | valueETC...
2   |         | value  |        | value  |        |        | valueETC...
3   |         |        |        |        |        | value  | valueETC...
..

Where NAME1 | NAME2 | NAME3 etc are now the column headers.

I'm guessing a pivot and a 'select distinct PROPERTY_NAME from MycoolTable' but can't seem to put the two together.

This is as far as i got: (no server_id) column and all nulls everywhere) Clearly i'm stupid :D

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

select @cols = STUFF((SELECT distinct 
           ',' + QUOTENAME(PROPERTY_NAME)
               FROM [BSARA_DW_DB].[dbo].[SERVER_PROPERTY]
               FOR XML PATH(''), TYPE
               ).value('.', 'NVARCHAR(MAX)') 
               ,1,1,'');

SET @query = 'SELECT  '+ @cols + ' from 
         (
            SELECT SERVER_ID, PROPERTY_NAME, PROPERTY_CHAR_VAL
            FROM [BSARA_DW_DB].[dbo].[SERVER_PROPERTY]
        ) x
        pivot 
        (
            MAX(SERVER_ID)
            for PROPERTY_CHAR_VAL in (' + @cols + ')
        ) p ';

execute(@query)

Many thanks, Mike

解决方案

The problem with your current query is with the line:

MAX(SERVER_ID)

You want to display the PROPERTY_CHAR_VAL for each PROPERTY_NAME instead. The SERVER_ID will be a part of the final result as a column.

Sometimes when you are working with PIVOT is is easier to write the code first with the values hard-coded similar to:

select id, name1, name2, name3, name4
from
(
  select id, property_name, property_value
  from yourtable
) d
pivot
(
  max(property_value)
  for property_name in (name1, name2, name3, name4)
) piv;

See SQL Fiddle with Demo.

Once you have a version that has the correct logic, then you can convert it to dynamic SQL to get the result. This will create a sql string that will be executed and it will include all of your new columns names.

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

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

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

execute sp_executesql @query;

See SQL Fiddle with Demo. Both will give a result:

| ID |  NAME1 |  NAME2 |  NAME3 |  NAME4 |  NAME6 |
|----|--------|--------|--------|--------|--------|
|  1 |  value |  value |  value | (null) | (null) |
|  2 | (null) |  value | (null) |  value | (null) |
|  3 | (null) | (null) | (null) | (null) |  value |

这篇关于MSSQL动态透视列值列为列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！