How to reorganize an sql query?

Posted on 2013-12-09
Medium Priority
Last Modified: 2013-12-14

I have a query that gives me the following information:

Code, ProductName, Size
  1       Blouse              S
  2       Blouse              S
  3       Skirt                  M
  4       Pants                L

The idea is to reorganize the information like this:

ProductName, SizeS, SizeM, SizeL
 Blouse               2         0         0
 Skirt                   0         1         0
 Pants                 0         0         1

Any ideas?

Question by:dimensionav
  • 3
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39706184
select productname,
sum (case when Size = 'S' then 1 else 0 end) SizeS
sum (case when Size = 'M' then 1 else 0 end) SizeM
sum (case when Size = 'L' then 1 else 0 end) SizeL
from <Table>
group by productName
LVL 35

Expert Comment

ID: 39706199
you might want to use pivot query

try something like this:

SELECT * FROM (SELECT Title,Code,'Size'+Size as sz FROM Table_1) Data
FOR sz IN([SizeS],[sizeM],[SizeL])) AS CountSize
LVL 35

Expert Comment

ID: 39706232
ged325, you are missing some commas
LVL 35

Accepted Solution

YZlat earned 2000 total points
ID: 39706237
oops, Title should be ProductName:

SELECT * FROM (SELECT ProductName,Code,'Size'+Size as sz FROM Table_1) Data
FOR sz IN([SizeS],[sizeM],[SizeL])) AS CountSize 

Open in new window


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question