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: 442467 More actions · Answer 2

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: 1004356 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)

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

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: 1004356 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)

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: 1004356 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)

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: 1004356 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)

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

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