Sql动态行列查询 [英] Sql dynamic row column query

问题描述

我在SQL表中有数据这样..

  RouteId RouteName  
  1  ROUTE1 
  1  ROUTE2 
  1  ROUTE3 
 

  Id用户名区域城市RouteId  
  1  ABC1 XYZ1 PQR1  1  
  2  ABC2 XYZ2 PQR1  1  
  3  ABC3 XYZ3 PQR1  2  
  4  ABC4 XYZ4 PQR2  2  
  5  ABC5 XYZ5 PQR2  2  
  6  ABC6 XYZ6 PQR3  2  
  7  ABC7 XYZ7 PQR4  3  
 

想要输出为..

  Route1 Route2 Route3  
 ABC1 ABC3 ABC7 
 ABC2 ABC4  NULL  
  NULL  ABC5  NULL  
  NULL  ABC6  NULL  
 

想要路由名称作为标题和UserName作为数据..

我的朋友可以在SQL中建议我查询吗？



什么我试过了：



我试过PIVOT ..但它给出了7个记录......

请帮帮我。 。

解决方案

路线表的第一个正确的RouteID如下。

 RouteId RouteName 
  1  ROUTE1 
  2  ROUTE2 
  3  ROUTE3 

然后使用以下查询获取结果。

 SELECT ROUTE1，ROUTE2，ROUTE3 
 FROM（
 
 select rd。*，r.RouteName from [RouteData] rd inner join [route] r on rd。[RouteId] = r。[RouteId] 
）as s 
 PIVOT 
（
 max（UserName）
 FOR RouteName IN（ROUTE1，ROUTE2，ROUTE3）
）AS pvt 

上面的动态查询

  DECLARE   @cols   AS   nvarchar （max）， @ query   AS   nvarchar的（最大）; 
 
  SELECT   @cols  = STUFF（
（
  SELECT  ' ，' + [RouteName ] 
  FROM  [route] 
  ORDER   BY  [RouteId] 
  FOR  XML PATH（' '）， TYPE  
）.value（' 。'，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '）; 
 
  PRINT   @cols ; 
 
  SET   @ query  = N '  SELECT' + @ cols + N ' 来自
（
选择rd。*，r.RouteName来自[RouteData] rd inner join [route] r on rd。[RouteId] = r。[RouteId] 
 
）x 
 pivot 
（
 max（UserName）
 for RouteName in（' + @cols + N ''  ）
）p'; 
 
  EXEC  sp_executesql  @ query ; 

输出：

 ROUTE1 ROUTE2 ROUTE3 
 ABC1 NULL NULL 
 ABC2 NULL NULL 
 NULL ABC3 NULL 
 NULL ABC4 NULL 
 NULL ABC5 NULL 
 NULL ABC6 NULL 
 NULL NULL ABC7 

假设：



1。下表名称[路线]

 RouteId RouteName 
 1 ROUTE1 
 1 ROUTE2 
 1 ROUTE3 

2。下表名称[RouteData]

 Id用户名区域城市RouteId 
  1  ABC1 XYZ1 PQR1  1  
  2  ABC2 XYZ2 PQR1  1  
  3  ABC3 XYZ3 PQR1  2  
  4  ABC4 XYZ4 PQR2  2  
  5  ABC5 XYZ5 PQR2  2  
  6  ABC6 XYZ6 PQR3  2  
  7  ABC7 XYZ7 PQR4  3  

I have Data in SQL Table Like this..

RouteId     RouteName
1        ROUTE1
1        ROUTE2
1        ROUTE3

Id     UserName    Area    City    RouteId
1        ABC1      XYZ1    PQR1    1
2        ABC2      XYZ2    PQR1    1
3        ABC3      XYZ3    PQR1    2
4        ABC4      XYZ4    PQR2    2
5        ABC5      XYZ5    PQR2    2
6        ABC6      XYZ6    PQR3    2
7        ABC7      XYZ7    PQR4    3

Want output as..

Route1     Route2     Route3
ABC1        ABC3      ABC7
ABC2        ABC4      NULL
NULL        ABC5      NULL
NULL        ABC6      NULL

Want Route Name as Header and UserName as Data..
Can any one of my friend Suggest me a Query for this in SQL??

What I have tried:

I tried PIVOT.. but it gives 7number of Records...
Please help me..

解决方案

First Correct RouteID of Route Table as follows.

RouteId	RouteName
1	ROUTE1
2	ROUTE2
3	ROUTE3

Then use following Query to obtain Result.

SELECT ROUTE1,ROUTE2,ROUTE3
FROM (
    
        select rd.*,r.RouteName from [RouteData] rd inner join [route] r on rd.[RouteId]=r.[RouteId]
) as s
PIVOT
(
    max(UserName)
    FOR RouteName IN (ROUTE1,ROUTE2,ROUTE3)
)AS pvt

Dynamic Query for above

DECLARE @cols AS nvarchar(max), @query AS nvarchar(max);

SELECT @cols = STUFF(
					(
						SELECT ','+[RouteName]
						FROM [route]
						ORDER BY [RouteId]
						FOR XML PATH(''), TYPE
					).value( '.', 'NVARCHAR(MAX)' ), 1, 1, '');

PRINT @cols;

SET @query = N'SELECT '+@cols+N' from 
             (
               select rd.*,r.RouteName from [RouteData] rd inner join [route] r on rd.[RouteId]=r.[RouteId]
  
            ) x
            pivot 
            (
                max(UserName)
                for RouteName in ('+@cols+N')
            ) p ';

EXEC sp_executesql @query;

Output:

ROUTE1	ROUTE2	ROUTE3
ABC1	NULL	NULL
ABC2	NULL	NULL
NULL	ABC3	NULL
NULL	ABC4	NULL
NULL	ABC5	NULL
NULL	ABC6	NULL
NULL	NULL	ABC7

Assumption:

1. Following Table Name [route]

RouteId     RouteName
1        ROUTE1
1        ROUTE2
1        ROUTE3

2. Following Table Name [RouteData]

Id     UserName    Area    City    RouteId
1        ABC1      XYZ1    PQR1    1
2        ABC2      XYZ2    PQR1    1
3        ABC3      XYZ3    PQR1    2
4        ABC4      XYZ4    PQR2    2
5        ABC5      XYZ5    PQR2    2
6        ABC6      XYZ6    PQR3    2
7        ABC7      XYZ7    PQR4    3

这篇关于Sql动态行列查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！