SQL 行到列(仅使用位值进行透视) [英] SQL rows to columns (Pivot with just bit values)

问题描述

我正在尝试将结果行转换为列，但仅使用位值.为了更好地解释我的意思，我的表看起来像这样；

I am trying to convert my resultant rows in to columns, but only with the bit values. To explain better what I mean, my table looks like this;

Id    Item
-------------------
1     TextLineA
1     TextLineB
2     TextLineA

而我想要实现的是这样的布局；

And what I am trying to achieve is a layout like this;

Id TextLineA  TextLineB
--------------------------
1     True       True
2     True       False

为我糟糕的格式道歉，但希望它传达了我正在寻找的东西.我尝试过 Pivot，但我没有任何可总结的东西，因为我只想要真/假值而不是总和.

Apologies for my poor formatting, but hopefully it conveys what I am looking for. I have tried Pivot but I don't have anything to sum over as I just want the true/false values rather than a sum.

此外，我有大约 15 列，我不介意静态地写出它们.任何指针将不胜感激！

Also, I have about 15 columns and I don't mind statically writing them out. Any pointers would be greatly appreciated!

推荐答案

这种方法可以解决这个问题:

This sort of approach will solve this:

DECLARE @Example TABLE (Id int, Item varchar(20))

INSERT INTO @Example (ID, Item)
SELECT 1, 'TextLineA' UNION ALL
SELECT 1, 'TextLineB' UNION ALL
SELECT 2, 'TextLineA' UNION ALL
SELECT 3, 'TextLineA' UNION ALL
SELECT 3, 'TextLineB' UNION ALL
SELECT 3, 'TextLineC' 

select ID, 
  MAX(CASE WHEN Item='TextLineA' THEN 1 ELSE 0 END) as TextLineA,
  MAX(CASE WHEN Item='TextLineB' THEN 1 ELSE 0 END) as TextLineB,
  MAX(CASE WHEN Item='TextLineC' THEN 1 ELSE 0 END) as TextLineC
FROM @Example
GROUP BY ID

结果:

ID  TextLineA   TextLineB   TextLineC
1   1           1           0
2   1           0           0
3   1           1           1

其中 1 为真，0 为假.

Where 1 is true and 0 is false.

这篇关于SQL 行到列(仅使用位值进行透视)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！