August 20, 2010 at 12:21 am
Dear Sir,
i have this query
select row_number() OVER (partition BY ledger_name ORDER BY ledger_name ) AS ROWID,
finance.tbl_Ledger_Master.ledger_name ,a.Attribute_centre_name 'Attribute Center',
b.Attribute_centre_name 'Attribute Value' from finance.tbl_Attribute_Master
inner join finance.tbl_Attribute_Centre a on
finance.tbl_Attribute_Master.attribute_center=a.id
inner join finance.tbl_Attribute_Centre b on
finance.tbl_Attribute_Master.attribute_value=b.id
inner join finance.tbl_Ledger_Master on
finance.tbl_Attribute_Master.ledger_id=finance.tbl_Ledger_Master.id
which gives result as
1Cash Branch Kandivali
2Cash DivisionMumbai
1PrashantBranchKandivali
2PrashantDivisionDelhi
1PratikBranchThane
2PratikDivisionDelhi
1RahulBranchKandivali
2RahulDivisionMumbai
in this result i want to avoid repeatation of ledger_name as cash,Prashant,pratik etc
is their any advanced technique in sql server so that i can get desired output as
Cash Branch Kandivali
DivisionMumbai
PrashantBranchKandivali
DivisionDelhi
PratikBranchThane
DivisionDelhi
RahulBranchKandivali
DivisionMumbai
plz send me solution
thanx in advance...
August 25, 2010 at 1:24 pm
Can you supply DDL, sample data and expected results? Not really sure what you want or how you want it to appear. See the link in my signature to show how to post to this forum.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 26, 2010 at 4:07 am
Hint:
CASE WHEN ROW_NUMBER() OVER(PARTITION BY ledger_name ORDER BY ledger_name) = 1 THEN
finance.tbl_Ledger_Master.ledger_name
ELSE
''
END
August 26, 2010 at 4:34 am
I think your requirement would best be fulfilled in your presentation layer rather than in the data layer. I would recommend Reporting Services for presenting data in this format.
John
August 26, 2010 at 4:44 am
John Mitchell-245523 (8/26/2010)
I think your requirement would best be fulfilled in your presentation layer rather than in the data layer. I would recommend Reporting Services for presenting data in this format.John
Agreed. If only we knew how the data is presented? 🙂
August 26, 2010 at 4:56 am
nigel. (8/26/2010)
Agreed. If only we knew how the data is presented? 🙂
Nigel
I think I understand what is required - the data is grouped according to common headers such as "2 Cash" and "1 Prashant". Where those headers have more than one row each, the header should only appear once. So, something like this, where the group headers are "A B" and "I J":
[font="Courier New"]A B C D
- - E F
- - G H
I J K L
- - M N
- - O P[/font]
Hope that makes sense to you.
John
August 26, 2010 at 5:02 am
John,
Yeah I get that.
What I was hoping for was the OP to give us more detail, whether he's using SSRS or a Web Page or whatever to present the data. We live in hope 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply