如何使用C＃或Sql Server查询在下面显示数据下面的格式 [英] How Can I Format Below Data As Shown In Below Using C# Or Sql Server Query

问题描述

我的表格中的数据如下所示格式

喜欢india是anddhrapradesh的父母是

的父母Nellore和Nellore是pincode的父母

i have data in table as shown in below format
like india is parent of anddhrapradesh is parent of
Nellore and Nellore is Parent of pincode

   ID     ParentID       TYpe               TExt
 1	  0	           Country       	India
2 	  1	           state	       AndhraPradesh
3	  2	           City	               Nellore
4	  3	           Pincode  	       524001
5     0	         Country	        Pakisthan
6     5           Sate                PakState
7     6           City                 Hyderabad
8     7          PinCode               251140

i希望在网格视图中显示以上数据，如下所示

i want display the above data in grid view as shown in below

country          State                 City 
--------          -------            --------
 india            AndhraPradesh         Hyderabad

please guys i tried all the ways but i didn't got that one please help me out if not i will be fired 

:(

提前感谢

:(
thanks in advance

推荐答案

可能有点迟到阻止你被解雇但这个解决方案有效



首先创建一个递归的CTE，它将遍历层次结构并确定终极的父级每个项目。

Probably a bit late to stop you being fired but this solution works

First create a recursive CTE that will traverse the hierarchy and determine the "ultimate" parent of each item.

;WITH CTE AS (
	SELECT ID, ParentID, [TYpe], [TExt]
,MtoE = CAST(ID AS VARCHAR(MAX))
FROM demo
WHERE ParentID = 0
UNION ALL
SELECT e.ID, e.ParentID, e.[TYpe], e.[TExt]
, MtoE
FROM demo e
INNER JOIN CTE ecte ON ecte.ID = e.ParentID
)
SELECT EC.ID AS ID, EC.ParentID, EC.[TYpe], EC.[TExt], MtoE
FROM CTE EC
LEFT JOIN demo E ON EC.ParentID = E.ID
order by MtoE

这会产生结果

This produces the results

Id      Parent  TYpe    TExt             MtoE
1	0	Country	India	         1
2	1	state	AndhraPradesh	 1
3	2	City	Nellore	         1
4	3	Pincode	524001	         1
5	0	Country	Pakisthan	 5
6	5	State	PakState	 5
7	6	City	Hyderabad	 5
8	7	PinCode	251140	         5

派生列MtoE是每组数据的顶级级别的ID。该数据集可以输入到一个数据集而不是上面的简单查询

The derived column MtoE is the ID of the "top" level for each set of data. That dataset can be fed into a pivot instead of the simple query above

;WITH CTE AS (
	SELECT ID, ParentID, [TYpe], [TExt]
,MtoE = CAST(ID AS VARCHAR(MAX))
FROM demo
WHERE ParentID = 0
UNION ALL
SELECT e.ID, e.ParentID, e.[TYpe], e.[TExt]
, MtoE
FROM demo e
INNER JOIN CTE ecte ON ecte.ID = e.ParentID
)
SELECT Country, [state], City, Pincode
FROM (
    SELECT MtoE,[Type], [TExt]
    FROM CTE
) as s
PIVOT
(
    MAX([TExt])
    FOR [TYpe] IN (Country, [state], City, Pincode)
)AS pvt

产生结果

Which yields the results

Country         State           City           Pincode
India	        AndhraPradesh	Nellore	        524001
Pakisthan	PakState	Hyderabad	251140

这篇关于如何使用C＃或Sql Server查询在下面显示数据下面的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！