如何转动此查询？

本文介绍了如何转动此查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我做了一些谷歌搜索，但我错过了一些东西，并希望这里的善良的人可以帮助。简单地说，如何调整此查询的结果，以便每个 Label 都是一列，并且相应的值 标签列中的值？

I've done some googling, but I am missing something and hoping the kind folk here can help. Simply, how can I pivot the results of this query, so that each Label is a column, and the corresponding Value a value in that Label's column?

select dets.Label , dets.Value from (select sc.SubmissionPkId , sn.Name , rs.Label , rs.Value from CanvasResponse rs inner join CanvasScreen sn on rs.ScreenPkId = sn.PkId inner join CanvasSection sc on sn.SectionPkId = sc.PkId where sn.Name = 'Seller Details' or sn.Name = 'Property Details' and Label in ( 'OwnerName', 'OwnetEmail', 'OwnerContactNumber', 'Price', 'StreetNumber', 'StreetName', 'Suburb', 'Town' ) ) dets

有趣的是，在数据库级别，它更有用，但它纯粹是用于演示，而且我很多r在服务器上的旋转比在WPF代码中的旋转。 数据，预透视，看起来像这样，给出或取几个字段。我，例如想要 OwnerName ， OwnetEmail （sic）等作为列。

Funny that at database level, it's more useful as is, but it is purely for presentation, and I'd much rather pivot on the server than in WPF code. The data, pre-pivot, looks like this, give or take a few fields. I e.g. want OwnerName , OwnetEmail (sic) etc. as columns.

OwnerName: Sipes v Vuuren OwnetEmail: OwnerContactNumber: 079654324 OwnerID: Town: Pretoria East (South) Suburb: Moreleta Park StreetName: Paul street StreetNumber: 123

推荐答案

这样的事情（警告 - 我无法正确测试所以可能有奇怪的拼写错误 - 使用临时表来模仿你的查询 dets ） Something like this (caveat - I haven't been able to test this properly so there might be the odd typo - used a temp table to mimic your query dets) WITH dets as ( select sc.SubmissionPkId , sn.Name , rs.Label , rs.Value from CanvasResponse rs inner join CanvasScreen sn on rs.ScreenPkId = sn.PkId inner join CanvasSection sc on sn.SectionPkId = sc.PkId where sn.Name = 'Seller Details' or sn.Name = 'Property Details' and Label in ( 'OwnerName', 'OwnetEmail', 'OwnerContactNumber', 'Price', 'StreetNumber', 'StreetName', 'Suburb', 'Town' ) ) SELECT * FROM ( SELECT [SubmissionPkId], [Label], [Value] FROM dets ) as s PIVOT ( MAX([Value]) FOR [Label] IN (OwnerName, OwnetEmail, OwnerContactNumber, OwnerID, Town, Suburb, StreetName, StreetNumber ) )AS p

试一试，告诉我哪里弄错了！

Give it a go and let me know where I got it wrong!