|
|
You're in the
sql server programming
group:Urgent SQL query help, please !
sql server programming:
In the future, please post DDL (create statements) and data (insert statements). This will help ensure a more timely and accurate response to your problem. SELECT COALESCE(a.Store, b.Store, c.Store, d.Store) AS Store, COALESCE(a.Category, b.Category, c.Category, d.Category) AS Category, a.Amount AS SaleLY, b.Amount AS SaleTY, c.Amount AS ReturnsLY, d.Amount AS ReturnsTY FROM Transactions a FULL JOIN Transactions b ON a.Store = b.Store AND a.Category = b.Category AND a.Subcategory = b.Subcategory FULL JOIN Transactions c ON a.Store = c.Store AND a.Category = c.Category AND a.Subcategory = c.Subcategory FULL JOIN Transactions d ON a.Store = d.Store AND a.Category = d.Category AND a.Subcategory = d.Subcategory WHERE a.TYLY = '2004' AND a.TransactionType = 'Sale' AND b.TYLY = '2005' AND b.TransactionType = 'Sale' AND c.TYLY = '2004' AND c.TransactionType = 'Return' AND d.TYLY = '2005' AND d.TransactionType = 'Return' -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Alex Nitulescu" <REMOVETHIScuca_macaii2000@yahoo.com> wrote in message news:%23axHOzB4FHA.4076@TK2MSFTNGP15.phx.gbl... >I have a table which is as follows: (Grouped by TY/LY, TransactionType, >Store, Category and Subcategory) - (TY/LY stands for ThisYear/LastYear) > > TYLY TransactionType Store Category Subcategory Amount > ----------------------------------------------------------------------- > 2005 Sale A 1 11 110 > 2005 Return A 1 11 115 > 2005 Sale A 1 12 120 > 2005 Return A 1 12 125 > 2005 Sale B 2 21 210 > 2005 Return B 2 21 215 > 2005 Sale B 2 22 220 > 2005 Return B 2 22 225 > --------------- and the same for last year: > 2004 Sale A 1 11 310 > 2004 Return A 1 11 315 > 2004 Sale A 1 12 320 > 2004 Return A 1 12 325 > 2004 Sale B 2 21 410 > 2004 Return B 2 21 415 > 2004 Sale B 2 22 420 > 2004 Return B 2 22 425 > > > I would need to transpose this table so that I obtain the following table > structure: > > Store Category Subcategory SaleTY SaleLY ReturnsTY > ReturnsLY > ---------------------------------------------------------------------- > A 1 11 110 310 115 > 315 > A 1 12 120 320 125 > 325 > ............................ > B 2 21 210 410 215 > 415 > B 2 22 220 420 225 > 425 > > and so on, where TY stands for This Year and LY stands for LY. > > How do I do that with queries, please (essentially, Year and > TransactionType becoming columns from rows) ? >
I have a table which is as follows: (Grouped by TY/LY, TransactionType,
Store, Category and Subcategory) - (TY/LY stands for ThisYear/LastYear) TYLY TransactionType Store Category Subcategory Amount ----------------------------------------------------------------------- 2005 Sale A 1 11 110 2005 Return A 1 11 115 2005 Sale A 1 12 120 2005 Return A 1 12 125 2005 Sale B 2 21 210 2005 Return B 2 21 215 2005 Sale B 2 22 220 2005 Return B 2 22 225 --------------- and the same for last year: 2004 Sale A 1 11 310 2004 Return A 1 11 315 2004 Sale A 1 12 320 2004 Return A 1 12 325 2004 Sale B 2 21 410 2004 Return B 2 21 415 2004 Sale B 2 22 420 2004 Return B 2 22 425 I would need to transpose this table so that I obtain the following table structure: Store Category Subcategory SaleTY SaleLY ReturnsTY ReturnsLY ---------------------------------------------------------------------- A 1 11 110 310 115 315 A 1 12 120 320 125 325 ............................. B 2 21 210 410 215 415 B 2 22 220 420 225 425 and so on, where TY stands for This Year and LY stands for LY. How do I do that with queries, please (essentially, Year and TransactionType becoming columns from rows) ?
Thanks a lot - I will try that out ASAP, and let you know how it's working
out. Alex [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:OlRFtEC4FHA.2432@TK2MSFTNGP10.phx.gbl... > One method below, assuming SQL Server 2000. > > In the future, please post DDL (create statements) and data (insert > statements). This will help ensure a more timely and accurate response to > your problem. > > SELECT > COALESCE(a.Store, b.Store, c.Store, d.Store) AS Store, > COALESCE(a.Category, b.Category, c.Category, d.Category) AS Category, > a.Amount AS SaleLY, > b.Amount AS SaleTY, > c.Amount AS ReturnsLY, > d.Amount AS ReturnsTY > FROM Transactions a > FULL JOIN Transactions b ON > a.Store = b.Store AND > a.Category = b.Category AND > a.Subcategory = b.Subcategory > FULL JOIN Transactions c ON > a.Store = c.Store AND > a.Category = c.Category AND > a.Subcategory = c.Subcategory > FULL JOIN Transactions d ON > a.Store = d.Store AND > a.Category = d.Category AND > a.Subcategory = d.Subcategory > WHERE > a.TYLY = '2004' AND > a.TransactionType = 'Sale' AND > b.TYLY = '2005' AND > b.TransactionType = 'Sale' AND > c.TYLY = '2004' AND > c.TransactionType = 'Return' AND > d.TYLY = '2005' AND > d.TransactionType = 'Return' > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Alex Nitulescu" <REMOVETHIScuca_macaii2000@yahoo.com> wrote in message > news:%23axHOzB4FHA.4076@TK2MSFTNGP15.phx.gbl... >>I have a table which is as follows: (Grouped by TY/LY, TransactionType, >>Store, Category and Subcategory) - (TY/LY stands for ThisYear/LastYear) >> >> TYLY TransactionType Store Category Subcategory Amount >> ----------------------------------------------------------------------- >> 2005 Sale A 1 11 110 >> 2005 Return A 1 11 115 >> 2005 Sale A 1 12 120 >> 2005 Return A 1 12 125 >> 2005 Sale B 2 21 210 >> 2005 Return B 2 21 215 >> 2005 Sale B 2 22 220 >> 2005 Return B 2 22 225 >> --------------- and the same for last year: >> 2004 Sale A 1 11 >> 310 >> 2004 Return A 1 11 315 >> 2004 Sale A 1 12 >> 320 >> 2004 Return A 1 12 325 >> 2004 Sale B 2 21 >> 410 >> 2004 Return B 2 21 415 >> 2004 Sale B 2 22 >> 420 >> 2004 Return B 2 22 425 >> >> >> I would need to transpose this table so that I obtain the following table >> structure: >> >> Store Category Subcategory SaleTY SaleLY ReturnsTY >> ReturnsLY >> ---------------------------------------------------------------------- >> A 1 11 110 310 115 >> 315 >> A 1 12 120 320 125 >> 325 >> ............................ >> B 2 21 210 410 215 >> 415 >> B 2 22 220 420 225 >> 425 >> >> and so on, where TY stands for This Year and LY stands for LY. >> >> How do I do that with queries, please (essentially, Year and >> TransactionType becoming columns from rows) ? >> > >
Dan, I have a slight problem - it works ALMOST great, but what If I don't
have a record of type TYLY = '2004' AND TransactionType = 'Sale' for a specific Store/Categ/Subcateg ? Currently, for instance, for Store X I had NO Sales whatsoever in year 2004 for a specific Categ and Subcateg, so I have that record only for 2005. When I run this query, the record for 2005 is excluded ! Thanks a lot for your help, really ! Alex. [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:OlRFtEC4FHA.2432@TK2MSFTNGP10.phx.gbl... > One method below, assuming SQL Server 2000. > > In the future, please post DDL (create statements) and data (insert > statements). This will help ensure a more timely and accurate response to > your problem. > > SELECT > COALESCE(a.Store, b.Store, c.Store, d.Store) AS Store, > COALESCE(a.Category, b.Category, c.Category, d.Category) AS Category, > a.Amount AS SaleLY, > b.Amount AS SaleTY, > c.Amount AS ReturnsLY, > d.Amount AS ReturnsTY > FROM Transactions a > FULL JOIN Transactions b ON > a.Store = b.Store AND > a.Category = b.Category AND > a.Subcategory = b.Subcategory > FULL JOIN Transactions c ON > a.Store = c.Store AND > a.Category = c.Category AND > a.Subcategory = c.Subcategory > FULL JOIN Transactions d ON > a.Store = d.Store AND > a.Category = d.Category AND > a.Subcategory = d.Subcategory > WHERE > a.TYLY = '2004' AND > a.TransactionType = 'Sale' AND > b.TYLY = '2005' AND > b.TransactionType = 'Sale' AND > c.TYLY = '2004' AND > c.TransactionType = 'Return' AND > d.TYLY = '2005' AND > d.TransactionType = 'Return' > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Alex Nitulescu" <REMOVETHIScuca_macaii2000@yahoo.com> wrote in message > news:%23axHOzB4FHA.4076@TK2MSFTNGP15.phx.gbl... >>I have a table which is as follows: (Grouped by TY/LY, TransactionType, >>Store, Category and Subcategory) - (TY/LY stands for ThisYear/LastYear) >> >> TYLY TransactionType Store Category Subcategory Amount >> ----------------------------------------------------------------------- >> 2005 Sale A 1 11 110 >> 2005 Return A 1 11 115 >> 2005 Sale A 1 12 120 >> 2005 Return A 1 12 125 >> 2005 Sale B 2 21 210 >> 2005 Return B 2 21 215 >> 2005 Sale B 2 22 220 >> 2005 Return B 2 22 225 >> --------------- and the same for last year: >> 2004 Sale A 1 11 >> 310 >> 2004 Return A 1 11 315 >> 2004 Sale A 1 12 >> 320 >> 2004 Return A 1 12 325 >> 2004 Sale B 2 21 >> 410 >> 2004 Return B 2 21 415 >> 2004 Sale B 2 22 >> 420 >> 2004 Return B 2 22 425 >> >> >> I would need to transpose this table so that I obtain the following table >> structure: >> >> Store Category Subcategory SaleTY SaleLY ReturnsTY >> ReturnsLY >> ---------------------------------------------------------------------- >> A 1 11 110 310 115 >> 315 >> A 1 12 120 320 125 >> 325 >> ............................ >> B 2 21 210 410 215 >> 415 >> B 2 22 220 420 225 >> 425 >> >> and so on, where TY stands for This Year and LY stands for LY. >> >> How do I do that with queries, please (essentially, Year and >> TransactionType becoming columns from rows) ? >> > >
Below is another technique using correlated subqueries and a derived table.
This ought to do the job, assuming that Store, Category, Subcategory, TYLY and TransactionType uniquely identify a row. SELECT Store, Category, SubCategory, (SELECT Amount FROM Transactions b WHERE a.Store = b.Store AND a.Category = b.Category AND a.SubCategory = b.SubCategory AND b.TYLY = '2004' AND b. TransactionType = 'Sale') AS SalesLY, (SELECT Amount FROM Transactions b WHERE a.Store = b.Store AND a.Category = b.Category AND a.SubCategory = b.SubCategory AND b.TYLY = '2005' AND b.TransactionType = 'Sale') AS SalesTY, (SELECT Amount FROM Transactions b WHERE a.Store = b.Store AND a.Category = b.Category AND a.SubCategory = b.SubCategory AND b.TYLY = '2004' AND b.TransactionType = 'Return') AS ReturnsLY, (SELECT Amount FROM Transactions b WHERE a.Store = b.Store AND a.Category = b.Category AND a.SubCategory = b.SubCategory AND b.TYLY = '2005' AND b.TransactionType = 'Return') AS ReturnsTY FROM (SELECT a.Store, a.Category, a.SubCategory FROM Transactions a GROUP BY a.Store, a.Category, a.SubCategory) AS a -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Alex Nitulescu" <REMOVETHIScuca_macaii2000@yahoo.com> wrote in message news:%23nbyI1C4FHA.2196@TK2MSFTNGP10.phx.gbl... > Dan, I have a slight problem - it works ALMOST great, but what If I don't > have a record of type > TYLY = '2004' AND TransactionType = 'Sale' for a specific > Store/Categ/Subcateg ? > > Currently, for instance, for Store X I had NO Sales whatsoever in year > 2004 for a specific Categ and Subcateg, so I have that record only for > 2005. When I run this query, the record for 2005 is excluded ! > > Thanks a lot for your help, really ! > > Alex. > > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:OlRFtEC4FHA.2432@TK2MSFTNGP10.phx.gbl... >> One method below, assuming SQL Server 2000. >> >> In the future, please post DDL (create statements) and data (insert >> statements). This will help ensure a more timely and accurate response >> to your problem. >> >> SELECT >> COALESCE(a.Store, b.Store, c.Store, d.Store) AS Store, >> COALESCE(a.Category, b.Category, c.Category, d.Category) AS Category, >> a.Amount AS SaleLY, >> b.Amount AS SaleTY, >> c.Amount AS ReturnsLY, >> d.Amount AS ReturnsTY >> FROM Transactions a >> FULL JOIN Transactions b ON >> a.Store = b.Store AND >> a.Category = b.Category AND >> a.Subcategory = b.Subcategory >> FULL JOIN Transactions c ON >> a.Store = c.Store AND >> a.Category = c.Category AND >> a.Subcategory = c.Subcategory >> FULL JOIN Transactions d ON >> a.Store = d.Store AND >> a.Category = d.Category AND >> a.Subcategory = d.Subcategory >> WHERE >> a.TYLY = '2004' AND >> a.TransactionType = 'Sale' AND >> b.TYLY = '2005' AND >> b.TransactionType = 'Sale' AND >> c.TYLY = '2004' AND >> c.TransactionType = 'Return' AND >> d.TYLY = '2005' AND >> d.TransactionType = 'Return' >> >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Alex Nitulescu" <REMOVETHIScuca_macaii2000@yahoo.com> wrote in message >> news:%23axHOzB4FHA.4076@TK2MSFTNGP15.phx.gbl... >>>I have a table which is as follows: (Grouped by TY/LY, TransactionType, >>>Store, Category and Subcategory) - (TY/LY stands for ThisYear/LastYear) >>> >>> TYLY TransactionType Store Category Subcategory Amount >>> ----------------------------------------------------------------------- >>> 2005 Sale A 1 11 110 >>> 2005 Return A 1 11 115 >>> 2005 Sale A 1 12 120 >>> 2005 Return A 1 12 125 >>> 2005 Sale B 2 21 >>> 210 >>> 2005 Return B 2 21 215 >>> 2005 Sale B 2 22 >>> 220 >>> 2005 Return B 2 22 225 >>> --------------- and the same for last year: >>> 2004 Sale A 1 11 >>> 310 >>> 2004 Return A 1 11 315 >>> 2004 Sale A 1 12 >>> 320 >>> 2004 Return A 1 12 325 >>> 2004 Sale B 2 21 >>> 410 >>> 2004 Return B 2 21 >>> 415 >>> 2004 Sale B 2 22 >>> 420 >>> 2004 Return B 2 22 425 >>> >>> >>> I would need to transpose this table so that I obtain the following >>> table structure: >>> >>> Store Category Subcategory SaleTY SaleLY ReturnsTY >>> ReturnsLY >>> ---------------------------------------------------------------------- >>> A 1 11 110 310 115 >>> 315 >>> A 1 12 120 320 125 >>> 325 >>> ............................ >>> B 2 21 210 410 215 >>> 415 >>> B 2 22 220 420 225 >>> 425 >>> >>> and so on, where TY stands for This Year and LY stands for LY. >>> >>> How do I do that with queries, please (essentially, Year and >>> TransactionType becoming columns from rows) ? >>> >> >> > >
Thanks a lot, Dan, you really saved me last night ! I was desperate for
not obtaining the data I needed, and the thing was due today, and it was already almost midnight ! Thank you again. Alex Nitulescu PS. Right now I am at my day job, so I cannot test your suggestion yet - I have no access to my computer. But I'll try it out as soon as I get home. PS. Last night I stood up up to 5:30 AM, and I solved the problem by manipulating the data in the table (in the Insert used to populate that table) - I had some records with NULL in them so obviously conditions like (a.category = b.category) (or the condition (a.subcategory = b.subcategory)) were not working properly because both a.category and b.category were NULL. So I defined a default in those fields instead and I used the newly found keyword "COALESCE" :-)))) to insert data in the table, so now I'm not missing any more records.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |