如何在SQL Server中将行转换为列 [英] How to convert row to column in sql server

问题描述

我想在sql server中将行转换为列，我尝试了数据透视/取消透视概念，但无法获得结果.

我有一个包含两列的表，记录如下.

35球
35猫
35苹果
35狗
36板球
36足球
36曲棍球

我想将其转换为两行，一行35以下，第二行36 a以下

35球形猫苹果狗
36板球足球曲棍球null

Hi,

I want to convert row to column in sql server, I tried pivot/unpivot concept, could not get the result.

I have a table with two columns and records as below.

35 Ball
35 Cat
35 Apple
35 Dog
36 Cricket
36 Football
36 Hockey

I want to convert it into two rows, one row for 35. and 2nd row for 36 a below

35 Ball Cat Apple Dog
36 Cricket FootBall Hockey null

推荐答案

这很简单:

It''s pretty simple:

SELECT
     YourNumberColumnName, 
     STUFF(
         (SELECT DISTINCT ' ' + YourItemsColumnName 
          FROM MyTable
          WHERE YourNumberColumnName = t.YourNumberColumnName
          FOR XML PATH (''))
          , 1, 1, '')  AS ItemsList
FROM MyTable AS t
GROUP BY YourNumberColumnName

[edit]标出您不希望使用逗号替换的空格-OriginalGriff [/edit]

[edit]Noticed you didn''t want commas, replaced with spaces - OriginalGriff[/edit]

您所拥有的数据不足以将行转换为列.一个大问题是，为什么鲍尔先于猫还是苹果.如果您有某种订购栏，那么可以这样做.例如，第三列描述第一列(35或26)中的值内的序数.

例如考虑以下情形

The data you have is insufficient for transforming the rows to columns. One big question is, why Ball is before Cat or Apple. If you would have some kind of ordering column then this could be done. For example a third column describing an ordinal within the value in first column (35 or 26).

For example consider following scenario

create table t2 (
   col1 int,
   col2 varchar(200),
   col3 int -- ordinal
);

insert into t2 values (35 ,'Ball'     ,2);
insert into t2 values (35 ,'Cat'      ,1);
insert into t2 values (35 ,'Apple'    ,4);
insert into t2 values (35 ,'Dog'      ,3);
insert into t2 values (36 ,'Cricket'  ,1);
insert into t2 values (36 ,'Football' ,4);
insert into t2 values (36 ,'Hockey'   ,2);

select a.col1,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 1) AS C1,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 2) AS C2,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 3) AS C3,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 4) AS C4
from (select distinct col1
      from t2) a

选择的结果将是:

The result for the select would be:

col1  C1       C2      C3    C4
----  -------  ------  ----  -------
35    Cat      Ball    Dog   Apple
36    Cricket  Hockey  NULL  Football

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