Solved

How to reorganize an sql query?

Posted on 2013-12-09
4
123 Views
Last Modified: 2013-12-14
HI

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?

Regards.
0
Comment
Question by:dimensionav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 40

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
0
 
LVL 35

Expert Comment

by:YZlat
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
PIVOT (COUNT(Code)
FOR sz IN([SizeS],[sizeM],[SizeL])) AS CountSize
0
 
LVL 35

Expert Comment

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

Accepted Solution

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

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

Open in new window

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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