Retrieve comma-separated list within group by

Question

Post reply

Retrieve comma-separated list within group by

t.hitendra

SSCommitted

Points: 1620
More actions
June 23, 2009 at 12:01 am

#135525

Hello folks,

I have two tables TblGRINMaster and TblIssueNoteDetail.

For one issue note, issuenotedetail can have multiple references of GRIN.

In my query I am trying to retrieve sum of issuedqty on the basis of Issue Note and StockCategory, that is achievable however this group contains multiple grnid's and I need to show the comma separated list of grnno's as another column. it's like

IssueNoteID StockCategoryID IssuedItemQty GRNNoList

1 1 100 GRN00001,GRN00002

Following is my sample data and attempted query.

create table #tmpIssueDetail(IssueNoteID int,StockCategoryID int,IssuedItemQty decimal(18,3),GRNID int)

insert into #tmpIssueDetail

SELECT '32','269','18.000','52' UNION ALL

SELECT '32','269','18.000','371' UNION ALL

SELECT '32','269','18.000','913' UNION ALL

SELECT '32','269','15.000','1379' UNION ALL

SELECT '32','269','15.000','2055' UNION ALL

SELECT '32','269','12.000','2446' UNION ALL

SELECT '32','269','18.000','3072'

create table #tmpGRN(GRNID int,GRNNO varchar(20));

insert into #tmpGRN

select '22','GRN000022'UNION ALL

select '52','GRN000052'UNION ALL

select '83','GRN000205'UNION ALL

select '93','GRN000214'UNION ALL

select '198','GRN000096'UNION ALL

select '213','GRN000128'UNION ALL

select '239','GRN000275'UNION ALL

select '371','GRN000315'UNION ALL

select '373','GRN000316'UNION ALL

select '582','GRN000467'UNION ALL

select '910','GRN000592'UNION ALL

select '913','GRN000595'UNION ALL

select '955','GRN000631'UNION ALL

select '975','GRN000653'UNION ALL

select '1100','GRN000749'UNION ALL

select '1101','GRN000750'UNION ALL

select '1379','GRN000893'UNION ALL

select '1578','GRN000924'UNION ALL

select '1811','GRN000984'UNION ALL

select '1878','GRN001044'UNION ALL

select '2055','GRN001103'UNION ALL

select '2196','GRN001185'UNION ALL

select '2446','GRN001370'UNION ALL

select '2686','GRN001432'UNION ALL

select '3052','GRN001627'UNION ALL

select '3072','GRN001647'UNION ALL

select '3402','GRN001777'

Select IssueNoteID,StockCategoryID,Sum(IssuedItemQty) as QtyIssued,( SELECT G.GRNNo + ','

FROM #tmpGRN AS G

WHERE G.GRNID = GM.GRNID

ORDER BY G.GRNNo

FOR XML PATH('')

) AS GRNNo

From #tmpIssueDetail isd inner join tblGRINMaster gm on isd.GRNID=gm.GRNID

group by IssueNoteID,StockCategoryID

drop table #tmpIssueDetail

drop table #tmpGRN

Thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

t.hitendra SSCommitted Points: 1620 More actions · Answer 1

Hi,

I think I got it right by changing the query.

select IssueNoteID,StockCategoryID,QtyIssued,Left(GRNNo,len(GRNNo)-1)

from (

Select IssueNoteID,StockCategoryID,Sum(IssuedItemQty) as QtyIssued,( SELECT G.GRNNo + ','

FROM #tmpGRN AS G inner join #tmpIssueDetail isd2 on g.GRNID=isd2.GRNID

WHERE isd.IssueNoteID = isd2.IssueNoteID and isd.StockCategoryID = isd2.StockCategoryID

group by G.GRNNo

ORDER BY G.GRNNo

FOR XML PATH('')

) AS GRNNo

From #tmpIssueDetail isd inner join tblGRINMaster gm on isd.GRNID=gm.GRNID

group by IssueNoteID,StockCategoryID

) tbl

But still I would like to know whether I can rewrite this query without having any triangular join in the query and make it quicker.

Please correct me if i'm wrong anywhere.

Thanks in advance.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442447

June 26, 2009 at 9:49 am

#1016139

I don't see anything in the your query that indicates that you have a triangular join. All criteria are using equality comparisions.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

I agree with Lynn. No sign of a triangular join in your query. Are you merely talking about the fact that you have a "derived table" in your FROM clause?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

t.hitendra SSCommitted Points: 1620 More actions · Answer 4

t.hitendra

SSCommitted

Points: 1620

June 29, 2009 at 12:00 am

#1016615

Hi Lynn & Jeff,

I made a mistake there is no traingular join. I don't quite remember why i mentioned it, I guess I was thinking about not having to write a 'subquery' for getting comma separated list.

Thanks anyways.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

In order to do the concatenation to create the CSV list, some form of sub-query either in the form of an actual sub-query or in the form of a UDF will be necessary.

What I'm curious about is why you are being required to produce the list as a single column CSV. This is a form of denormalization that usually shouldn't be done on the database side of the house. What are your business rules that require such a thing? I ask because there may be a better way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

t.hitendra SSCommitted Points: 1620 More actions · Answer 6

Hi Jeff,

Issue (Issue to dept/employee) of one single item may require to knock of balance qty of many grn's (purchase).

Like Issue Pen(50) - GRN1 10,GRN2 20 ,GRN3 20.

My client has asked for excise adjustment forms (exise is 'tax on manufacturing' in india). Where Issues will have to be adjusted based on purchase.

Now client wants to adjust amount (excise to be paid) based on

Issue,Item Category(parent of item) and also wants to see list of all GRN's used in one single column within a row in the grid.

So I used CSV, dunno how there can be a better way.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Thank you very much for taking the time to explain that to me. Most people pose what the want to solve for instead of posting what the problem is and they frequently get the wrong answer as a result.

I made the major change of not touching the GUI side of the world way back in 2002 so I can't actually begin to tell you exactly how to do this but, rumor has it that there's an expandable tree type of tool (I think it works in a grid, as well) available in things like C# and VB, etc. That would allow summaries to be exposed with + signs on them. If they see a problem in the summary, they can click the + sign to drill down and see the detail.

I believe that reporting services allows for the same type of thing but, again, that's on the GUI side of the house and I can't begin to tell you how to do it... I can only tell you that such a thing exits.

Another option (if you don't have a GUI), it to use a GROUP BY WITH ROLLUP to give subtotals for each group. Again, the advantage is that they can see the subtotal for those things you want to group and still see the detail. THAT I can tell you how to do in code if that's what you want.

Of course, if none of that is what they want and they just want what could be a very wide report (with a wrap on the CSV column), then the CSV solution will likely be just fine.

Again, thanks for identifying the actual business reason why you needed to do this. It's actually a rare pleasure to know why someone wants to do such a thing and it allows us to bring forth other suggestions that may (or may not) help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

t.hitendra SSCommitted Points: 1620 More actions · Answer 8

Hi Jeff,

Thanks Jeff for replying. I am quite surprised to see such a keen interest. Although I am sorry for only posting part of the problem.

Talking about extent denormalization, the csv list is not just to be shown in the grid but also to be stored in the target table (of that respective form) as asserted by manager (which i simply can't refute), its really beyond my expertise and authority now.

Real problem lies in organization politics where people sometimes refuse to understand why we need optimization,normalization, design patterns in code etc etc.

Yours was a great reply that I would surely follow in future if I can.And I am delighted that I was heard.

Thanks anyways

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Heh... I figured it was driven by management. As you know (especially because you said so :-)), storing such (CSV) data in any type of table other than maybe a reporting table is just asking for trouble. I know you can't refute it and I sure hope they don't hammer on you when things go wrong with that whole idea. Of course, if you're in a consulting capacity, it will mean extra billable hours. 😀

The reason I'm so interested is because I see this type of request time and again and, because of it's nature to have an ill effect on the database in the future, I'm always interested in finding out what the business reasons are so I can help people avoid such pitfalls. Of course, the one thing I can't help them avoid is draconian orders from the boss even though I try to help even there. :hehe:

I believe that having nested SELECTs in the FROM clause for this may not be the "optimal" way of doing this. I'll try to put together some code for this tonight after work (unless someone beats me to it).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 10

Lynn Pettis

SSC Guru

Points: 442447

June 30, 2009 at 8:39 am

#1017616

Slight problem with the code you provided. The table, tblGRINMaster, doesn't exist in the post. To really try and help, we need this table along with some sample data as well.

t.hitendra SSCommitted Points: 1620 More actions · Answer 11

Sorry Lynn,

It is #tmpGRN not tblGRINMaster. It somehow worked on my machine cause I had both of them. And as you can see I had put some rows

for #tmpGRN. I can't give you the exact table structures in the query because that would be too long, and i think this query nicely captures the abstraction needed to understand the problem.

I'll put the code here again -

create table #tmpIssueDetail(IssueNoteID int,StockCategoryID int,IssuedItemQty decimal(18,3),GRNID int)

insert into #tmpIssueDetail

SELECT '32','269','18.000','52' UNION ALL

SELECT '32','269','18.000','371' UNION ALL

SELECT '32','269','18.000','913' UNION ALL

SELECT '32','269','15.000','1379' UNION ALL

SELECT '32','269','15.000','2055' UNION ALL

SELECT '32','269','12.000','2446' UNION ALL

SELECT '32','269','18.000','3072'

create table #tmpGRN(GRNID int,GRNNO varchar(20));

insert into #tmpGRN

select '22','GRN000022'UNION ALL

select '52','GRN000052'UNION ALL

select '83','GRN000205'UNION ALL

select '93','GRN000214'UNION ALL

select '198','GRN000096'UNION ALL

select '213','GRN000128'UNION ALL

select '239','GRN000275'UNION ALL

select '371','GRN000315'UNION ALL

select '373','GRN000316'UNION ALL

select '582','GRN000467'UNION ALL

select '910','GRN000592'UNION ALL

select '913','GRN000595'UNION ALL

select '955','GRN000631'UNION ALL

select '975','GRN000653'UNION ALL

select '1100','GRN000749'UNION ALL

select '1101','GRN000750'UNION ALL

select '1379','GRN000893'UNION ALL

select '1578','GRN000924'UNION ALL

select '1811','GRN000984'UNION ALL

select '1878','GRN001044'UNION ALL

select '2055','GRN001103'UNION ALL

select '2196','GRN001185'UNION ALL

select '2446','GRN001370'UNION ALL

select '2686','GRN001432'UNION ALL

select '3052','GRN001627'UNION ALL

select '3072','GRN001647'UNION ALL

select '3402','GRN001777'

select IssueNoteID,StockCategoryID,QtyIssued,Left(GRNNo,len(GRNNo)-1)

from (

Select IssueNoteID,StockCategoryID,Sum(IssuedItemQty) as QtyIssued,( SELECT G.GRNNo + ','

FROM #tmpGRN AS G inner join #tmpIssueDetail isd2 on g.GRNID=isd2.GRNID

WHERE isd.IssueNoteID = isd2.IssueNoteID and isd.StockCategoryID = isd2.StockCategoryID

group by G.GRNNo

ORDER BY G.GRNNo

FOR XML PATH('')

) AS GRNNo

From #tmpIssueDetail isd inner join #tmpGRN gm on isd.GRNID=gm.GRNID

group by IssueNoteID,StockCategoryID

) tbl

DROP table #tmpIssueDetail

drop table #tmpGRN