how to get this result in tree view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to get this result in tree view

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 04:01:04

Hi all
Here how can i achieve tree view in this scenario
For Example


	Acute Patient (grand Parent)
	
	     CPT (Parent)
			
				1101
				1102
	     RevCode (Parent)
				AC36
				AC37

In some case there will be no grand Parent then parent should be act as Grand parent.
For Grand Parent i gave row_number to get ID's basing on that i need to get tree view

here is my query


SELECT 
	DISTINCT top 5 Sno = ROW_NUMBER() OVER (
			ORDER BY ECTCodeDescription DESC
			),
	ECTCodeDescription,
	CSEC.ECTHedisCodeTypeCode,
	ECT.ECTHedisTableName,
	ECTCode
FROM CodeSetHEDIS_ECTCode EC
INNER JOIN CodeSetECTHedisTable ECT ON 
ECT.ECTHedisTableID = EC.ECTHedisTableID
INNER JOIN CodeSetECTHedisCodeType CSEC 
ON CSEC.ECTHedisCodeTypeID = EC.ECTHedisCodeTypeID

WHERE ECT.ECTHedisTableID = 29 --AND ECTCodeDescription = 'Nonacute inpatient'
GROUP BY ECTCodeDescription,
	ECTCode,
	ECTHedisTableName,
	CSEC.ECTHedisCodeTypeCode

my resut set is coming like this


	Sno	ECTCodeDescription	ECTHedisCodeTypeCode		ECTCode
	1	Outpatient			CPTCODE						0510
	2	Outpatient			CPTCODE						0511
	3	NonAcute			RevCode						AC36
	4	NonAcute			RevCode						AC36
	5	NonAcute			RevCode						AC36

How can i get output like on the above query :


	Sno	Names		ID	
	1	Outpatient	NULL
	2	NonAcute	NULL
	3	CPTCODE		1
	4	0510		3
	5	0511		3
	6	RevCode		2
	7	AC36		6
	8	AC37		6
	9	AC38		6

suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 04:38:43

Can you post sample data in consumable format and also clear and exact output for that data....

For this kind of hierarchical queries you can use of CTEs in sql server

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 04:39:48

plz check my updated question

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 05:08:51

Can you post us the sample data of original tables, not your query results

Refer this link http://www.4guysfromrolla.com/webtech/071906-1.shtml
--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 05:17:58

yeah chandu this one his correct data from my original tables


	Sno	ECTCodeDescription	ECTHedisCodeTypeCode		ECTCode
	1	Outpatient			CPTCODE						0510
	2	Outpatient			CPTCODE						0511
	3	NonAcute			RevCode						AC36
	4	NonAcute			RevCode						AC36
	5	NonAcute			RevCode						AC36

and my required output


	Sno	Names		ID	
	1	Outpatient	NULL
	2	NonAcute	NULL
	3	CPTCODE		1
	4	0510		3
	5	0511		3
	6	RevCode		2
	7	AC36		6
	8	AC37		6
	9	AC38		6

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 05:19:47

No yar....
I asked for sample data of 3 tables CodeSetHEDIS_ECTCode, CodeSetECTHedisTable and CodeSetECTHedisCodeType

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 05:38:52

okay i will post you with in few minutes

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 05:52:45

CodeSetHEDIS_ECTCode


Sno	ECTCodeDescription  ECTHEDISCODETYPEID ECTHedisTableID
1	Outpatient		4		11			
2	Nonacute inpatient	2		23
3	ED			6		11
4	Acute inpatient		9		29

CodeSetECTHedisTable and CodeSetECTHedisCodeType keys referenced to table CodeSetHEDIS_ECTCode

CodeSetECTHedisTable



ECTHedisTableID	ECTHedisTableName
1				AAB-A
2				AAB-B
3				AAB-C
29 				AAB-D
4				ABA-A

CodeSetECTHedisCodeType


ECTHedisCodeTypeID	ECTHedisCodeTypeCode
1					CPT
2					CPT-CAT-II
3					CPT-Mod
4					REVCODE
5					ICD9-Diag

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 07:19:18

How come the ID of SNO >=7 are 6?
--Check this one


DECLARE @tab TABLE(	Sno	INT, ECTCodeDescription	VARCHAR(30), ECTHedisCodeTypeCode	VARCHAR(30), ECTCode VARCHAR(30))
INSERT INTO @tab
SELECT 1,	'Outpatient', 'CPTCODE', '0510' union all
SELECT 2,	'Outpatient', 'CPTCODE', '0511' union all
SELECT 3,	'NonAcute', 'RevCode', 'AC36' union all
SELECT 4,	'NonAcute', 'RevCode', 'AC37' union all
SELECT 5,	'NonAcute', 'RevCode', 'AC38'
/*How can i get output like on the above query :
	Sno	Names		ID	
	1	Outpatient	NULL
	2	NonAcute	NULL
	3	CPTCODE		1
	4	0510		3
	5	0511		3
	6	RevCode		2
	7	AC36		6
	8	AC37		6
	9	AC38		6*/

SELECT DISTINCT 
		Val
		,CASE WHEN K = 'ECTCodeDescription' THEN 0 ELSE DENSE_RANK() OVER(ORDER BY K DESC, Val) END ID
	 FROM @tab
	UNPIVOT (Val For K IN (ECTCodeDescription, ECTHedisCodeTypeCode, ECTCode)) U
ORDER BY ID

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 07:51:50

hey chandu it is giving result but not in expected way
i getting output like this by using your given query


Val	ID
NonAcute	0
Outpatient	0
CPTCODE	1
RevCode	2
0510	5
0511	6
AC36	7
AC37	8
AC38	9

but i need to show like this one


	Sno	Names		ID	
	1	Outpatient	NULL
	2	NonAcute	NULL
	3	CPTCODE		1
	4	0510		3
	5	0511		3
	6	RevCode		2
	7	AC36		6
	8	AC37		6
	9	AC38		6

just observe the difference between both CPTCODE(0510,0511) anbd revcode (AC36,AC37,AC38)

but it is giving like this CPTCODE,revcode,0510,0511AC36,AC37,AC38

suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 08:08:03

Hello,

I too know that result...
I need clarification on ID column... how it has 6 for these three values (AC36, AC37, AC38)

Do you know another thing you didn't give the column ECTCode in your sample data....

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-24 : 08:23:52

result is coming like this


Val	ID
NonAcute0
Outpatient0
CPTCODE	1
RevCode	2
0510	5
0511	6
AC36	7
AC37	8
AC38	9

i need to get like this


Val	ID
NonAcute0
Outpatient0
CPTCODE	1
0510	5
0511	6
RevCode	2
AC36	7
AC37	8
AC38	9

P.V.P.MOhan

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2013-05-24 : 12:16:37

Hi mohan123

Look like the important column in expected output is ID.

But the latest one is different from the first. So which one do you want?

quote:

How can i get output like on the above query :

Sno Names ID
1 Outpatient NULL
2 NonAcute NULL
3 CPTCODE 1
4 0510 3
5 0511 3
6 RevCode 2
7 AC36 6
8 AC37 6
9 AC38 6

i need to get like this

Val ID
NonAcute0
Outpatient0
CPTCODE 1
0510 5
0511 6
RevCode 2
AC36 7
AC37 8
AC38 9

Also, what is the version (2000, 2005, 2008 or 2012) ?

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-05-27 : 00:41:57

this the data for example


DECLARE @tab TABLE(	Sno	INT, ECTCodeDescription	VARCHAR(30), ECTHedisCodeTypeCode	VARCHAR(30), ECTCode VARCHAR(30))
INSERT INTO @tab
SELECT 1,	'Outpatient', 'CPTCODE', '0510' union all
SELECT 2,	'Outpatient', 'CPTCODE', '0511' union all
SELECT 3,	'NonAcute', 'RevCode', 'AC36' union all
SELECT 4,	'NonAcute', 'RevCode', 'AC37' union all
SELECT 5,	'NonAcute', 'RevCode', 'AC38'

but i need to show like this one


	Sno	Names		ID	
	1	Outpatient	NULL
	2	NonAcute	NULL
	3	CPTCODE		1
	4	0510		3
	5	0511		3
	6	RevCode		2
	7	AC36		6
	8	AC37		6
	9	AC38		6

my SSMS version is 2008R2

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 02:59:16

[code]--Is that ID column for the purpose of sorting only....? If yes you can do like the below
DECLARE @tab TABLE( Sno INT, ECTCodeDescription VARCHAR(60), ECTHedisCodeTypeCode VARCHAR(60), ECTCode VARCHAR(60))
INSERT INTO @tab
SELECT 1, 'Outpatient', 'CPTCODE', '0510' union all
SELECT 2, 'Outpatient', 'CPTCODE', '0511' union all
SELECT 3, 'NonAcute', 'RevCode', 'AC36' union all
SELECT 4, 'NonAcute', 'RevCode', 'AC37' union all
SELECT 5, 'NonAcute', 'RevCode', 'AC38'
/*How can i get output like on the above query :
Sno Names ID
1 Outpatient NULL
2 NonAcute NULL
3 CPTCODE 1
4 0510 3
5 0511 3
6 RevCode 2
7 AC36 6
8 AC37 6
9 AC38 6*/

SELECT DISTINCT
CASE WHEN K = 'ECTCodes' THEN ECTCode ELSE Val END Val
,CASE WHEN K = 'ECTCodeDescription' THEN 0
else DENSE_RANK() OVER(ORDER BY val)
END RN
FROM (SELECT *, CAST (ECTHedisCodeTypeCode+' '+ ECTCode AS VARCHAR(60))AS ECTCodes FROM @tab) T
UNPIVOT (Val For K IN (ECTCodeDescription, ECTHedisCodeTypeCode, ECTCodes)) U
ORDER BY RN[/code]

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources