August 19, 2013 at 10:43 am
Hi all
Suppose we have a table DATHIS with the following information:
[X1]---------[X2]-[X3]
2013-08-10, 1250, 75.0
2013-08-10, 1251, 82.0
2013-08-10, 1252, 35.0
2013-08-10, 1253, 45.0
2013-08-11, 1250, 75.1
2013-08-11, 1251, 82.2
2013-08-11, 1252, 50.3
2013-08-11, 1253, 50.4
2013-08-12, 1250, 75.5
2013-08-12, 1251, 82.6
2013-08-12, 1252, 45.7
2013-08-12, 1253, 55.8
We need the output like this:
[Y1]--------[Y2]--[Y3]-[Y4]
2013-08-10, 75.0, 82.0, 0
2013-08-11, 75.1, 82.2, 2
2013-08-12, 75.5, 82.6, 1
On Y4 column we need to count the number of X2 IN (1252, 1253) >= 50.
We did the following SQL command for Y1, Y2 and Y3:
SELECT X1 Y1, [1250] Y2, [1251] Y3 FROM
(SELECT X1, X2, X3 FROM DATHIS WHERE X1>='2013-08-10') H
PIVOT ( SUM(X3) FOR X2 in ([1250], [1251]) ) AS DH
ORDER BY X1
But, now how can we include Y4, for counting of number of rows >= 50.0 for X2 IN (1252, 1253)?
Any one have an idea for that, please?
Best Regards
Paulo
:hehe: :crazy:
August 19, 2013 at 10:57 am
Applying cross tabs with different aggregations it's quite easy.
Read the following articles to find more information on cross tabs
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
and read the article linked on my signature to follow best practices when posting sample data for your next posts.
Here's the code with the sample data in a CTE to test.
WITH SampleData(X1, X2, X3) AS(
SELECT '2013-08-10', 1250, 75.0 UNION ALL SELECT
'2013-08-10', 1251, 82.0 UNION ALL SELECT
'2013-08-10', 1252, 35.0 UNION ALL SELECT
'2013-08-10', 1253, 45.0 UNION ALL SELECT
'2013-08-11', 1250, 75.1 UNION ALL SELECT
'2013-08-11', 1251, 82.2 UNION ALL SELECT
'2013-08-11', 1252, 50.3 UNION ALL SELECT
'2013-08-11', 1253, 50.4 UNION ALL SELECT
'2013-08-12', 1250, 75.5 UNION ALL SELECT
'2013-08-12', 1251, 82.6 UNION ALL SELECT
'2013-08-12', 1252, 45.7 UNION ALL SELECT
'2013-08-12', 1253, 55.8)
SELECT X1 AS Y1,
MAX(CASE WHEN X2 = 1250 THEN X3 END) AS Y2,
MAX(CASE WHEN X2 = 1251 THEN X3 END) AS Y3,
SUM(CASE WHEN X2 IN( 1252, 1253) AND X3 >= 50 THEN 1 ELSE 0 END) AS Y4
FROM SampleData
GROUP BY X1
August 20, 2013 at 2:27 am
The url you give was excellent for understand and give the choice of way for my solutions.
:w00t:
August 20, 2013 at 8:45 am
I'm glad it helped you to learn more Although all credit should go to Jeff.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy