SQL Server 2008垂直数据转换为水平数据 [英] SQL Server 2008 Vertical data to Horizontal
问题描述
我很抱歉提出关于该主题的另一个问题，但是我已经阅读了许多有关此问题的答案，但似乎无法使它对我有用.
I apologize for submitting another question on this topic, but I've read through many of the answers on this and I can't seem to get it to work for me.
我需要三个表来加入和获取信息.其中一张表只有3列，并且垂直存储数据.我想将该数据转换为水平格式.
I have three tables I need to join and pull info on. One of the tables is only 3 columns and stores the data vertically. I would like to transpose that data to a horizontal format.
如果我只是加入并拉出，数据将如下所示:
The data will look like this if I just join and pull:
SELECT
a.app_id,
b.field_id,
c.field_name,
b.field_value
FROM table1 a
JOIN table2 b ON a.app_id = b.app_id
JOIN table3 c ON b.field_id = c.field_id --(table3 is a lookup table for field names)
结果:
app_id | field_id | field_name | field_value
-----------------------------------------------------
1234 | 101 | First Name | Joe
1234 | 102 | Last Name | Smith
1234 | 105 | DOB | 10/15/72
1234 | 107 | Mailing Addr | PO BOX 1234
1234 | 110 | Zip | 12345
1239 | 101 | First Name | Bob
1239 | 102 | Last Name | Johnson
1239 | 105 | DOB | 12/01/78
1239 | 107 | Mailing Addr | 1234 N Star Ave
1239 | 110 | Zip | 12456
相反，我希望它看起来像这样:
Instead, I would like it to look like this:
app_id | First Name | Last Name | DOB | Mailing Addr | Zip
--------------------------------------------------------------------------
1234 | Joe | Smith | 10/15/72 | PO BOX 1234 | 12345
1239 | Bob | Johnson | 12/01/78 | 1234 N Star Ave | 12456
过去，我只是依靠查找数据中所需的所有field_id并为每个数据创建CASE语句.用户使用的应用包含多个产品的数据，每个产品包含不同的字段.考虑到支持的产品数量和每个产品的字段数量(比我上面显示的基本示例要多得多)，查找它们并写出大块的CASE语句要花费很长时间.
In the past, I just resorted to looking up all the field_id's I needed in my data and created CASE statements for each one. The app the users are using contains data for multiple products, and each product contains different fields. Considering the number of products supported and the number of fields for each product (many, many more than the basic example I showed, above) it takes a long time to look them up and write out huge chunks of CASE statements.
我想知道是否有一些作弊代码来实现我所需的功能，而不必查找field_ids并将其写出来.我知道PIVOT函数可能是我想要的，但是，我似乎无法使其正常工作.
I was wondering if there's some cheat-code out there to achieve what I need without having to look up the field_ids and writing things out. I know the PIVOT function is likely what I'm looking for, however, I can't seem to get it to work correctly.
觉得你们可以帮忙吗?
推荐答案
You can use the PIVOT function to convert your rows of data into columns.
您的原始查询可用于检索所有数据，我对此所做的唯一更改将是排除列b.field_id，因为这会改变结果的最终显示.
Your original query can be used to retrieve all the data, the only change I would make to it would be to exclude the column b.field_id because this will alter the final display of the result.
如果您有一个已知的要转换为列的field_name值列表，则可以对查询进行硬编码:
If you have a known list of field_name values that you want to turn into columns, then you can hard-code your query:
select app_id,
[First Name], [Last Name], [DOB],
[Mailing Addr], [Zip]
from
(
SELECT
a.app_id,
c.field_name,
b.field_value
FROM table1 a
INNER JOIN table2 b
ON a.app_id = b.app_id
INNER JOIN table3 c
ON b.field_id = c.field_id
) d
pivot
(
max(field_value)
for field_name in ([First Name], [Last Name], [DOB],
[Mailing Addr], [Zip])
) piv;
请参见带有演示的SQL小提琴.
但是，如果您要为field_name提供未知数量的值，那么您将需要实现动态SQL以获取结果:
But if you are going to have an unknown number of values for field_name, then you will need to implement dynamic SQL to get the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name)
from Table3
group by field_name, Field_id
order by Field_id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT app_id,' + @cols + '
from
(
SELECT
a.app_id,
c.field_name,
b.field_value
FROM table1 a
INNER JOIN table2 b
ON a.app_id = b.app_id
INNER JOIN table3 c
ON b.field_id = c.field_id
) x
pivot
(
max(field_value)
for field_name in (' + @cols + ')
) p '
execute sp_executesql @query;
请参见带演示的SQL提琴.这两个都将产生结果:
See SQL Fiddle with Demo. Both of these this will give a result:
| APP_ID | FIRST NAME | LAST NAME | DOB | MAILING ADDR | ZIP |
------------------------------------------------------------------------
| 1234 | Joe | Smith | 10/15/72 | PO Box 1234 | 12345 |
| 1239 | Bob | Johnson | 12/01/78 | 1234 N Star Ave | 12456 |
这篇关于SQL Server 2008垂直数据转换为水平数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
