SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get this result in tree view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  04:01:04  Show Profile  Reply with Quote

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

Edited by - mohan123 on 05/24/2013 05:17:07

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/24/2013 :  04:38:43  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  04:39:48  Show Profile  Reply with Quote
plz check my updated question

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/24/2013 :  05:08:51  Show Profile  Reply with Quote
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

Edited by - bandi on 05/24/2013 05:10:23
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  05:17:58  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/24/2013 :  05:19:47  Show Profile  Reply with Quote
No yar....
I asked for sample data of 3 tables CodeSetHEDIS_ECTCode, CodeSetECTHedisTable and CodeSetECTHedisCodeType

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  05:38:52  Show Profile  Reply with Quote
okay i will post you with in few minutes

P.V.P.MOhan
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  05:52:45  Show Profile  Reply with Quote
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

Edited by - mohan123 on 05/24/2013 05:54:00
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/24/2013 :  07:19:18  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  07:51:50  Show Profile  Reply with Quote
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

Edited by - mohan123 on 05/27/2013 00:40:25
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/24/2013 :  08:08:03  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/24/2013 :  08:23:52  Show Profile  Reply with Quote
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

Edited by - mohan123 on 05/24/2013 08:30:53
Go to Top of Page

namman
Constraint Violating Yak Guru

USA
263 Posts

Posted - 05/24/2013 :  12:16:37  Show Profile  Reply with Quote
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) ?
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 05/27/2013 :  00:41:57  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/27/2013 :  02:59:16  Show Profile  Reply with Quote
--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


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000