在Sql中获取上述输出如何编写查询 [英] For Getting Above Output In Sql How Can I Write The Query

问题描述

表格记录如下

In table records as follows

Facid  Q1 Q2 Q3   Batchid
 1     2  3  4     B001
 2     4  4  5     B001
 1     5  1  2     B002
 2     4  5  3     B002
 1     4  1  2     B003
 2     5  4  3     B003

使用以上记录我希望使用sql查询输出如下

Using above records i want output as follows using sql query

             B001             B002             B003
Facid     Q1  Q2  Q3       Q1  Q2   Q3         Q1  Q2   Q3

 1        2   3   4         5   1   2           4   1    2
 2        4   4   5         4   5   3           5   4    3

获得以上输出如何编写SQL查询。



请帮助我。



问候

Narasiman

for getting a above output how can i write a sql query.

please help me.

Regards
Narasiman

推荐答案

专注于将数据作为额外标题获取是一个演示方面，并没有真正有趣，所以尽管你可以我认为交叉应用在这种情况下更灵活。特别是因为如果选择动态查询版本，它可以根据数据简单地添加交叉应用。

Was focusing on getting the data there as the extra header is a presentation aspect and not really interesting, so while you could do a pivot i think the cross apply is more agile in this circumstance. Especially because of it's inherent ability to simply add cross applies based on data if a dynamic query version is chosen.

declare @grp table(
    facid int
    )
insert into @grp(facid)
(select distinct facid from @tbl)

select facid, c.*, d.*, e.*
from @grp a
cross apply(
    select q1, q2, q3 from @tbl b where b.batchid = 'B001' and a.facid = b.facid
) c
cross apply(
    select q1, q2, q3 from @tbl b where b.batchid = 'B002' and a.facid = b.facid
) d
cross apply(
    select q1, q2, q3 from @tbl b where b.batchid = 'B003' and a.facid = b.facid
) e
/* you could generate query dynamically to make the batches based on actual data
and avoid hardcoding for instance from csharp  */

您好，



查看此...



在SQL查询中使用Pivot的简单方法 [ ^ ]



希望这会对你有所帮助。



干杯

Hi,

Check this...

Simple Way To Use Pivot In SQL Query[^]

Hope this will help you.

Cheers

在SQL中无法透视双头。

Pivoting double header is not possible in SQL.

这篇关于在Sql中获取上述输出如何编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！