October 6, 2010 at 5:12 am
Hi All, I new to these forums and SQL but you all seem very helpful so i thought i'd put you to the test!
there maybe technical terms i don't understand and this may be very simple but i'm very new to SQL so please bear with me. I have a table of customer records which i wish to re-create in a new layout. Below is an example of the current data structure and then how i want to recreate it.
Data now:
Cust_ID YearMonth
588DE245D9494A820101
588DE245D9494A820102
588DE245D9494A820103
588DE245D9494A820104
588DE252CD6F44C20103
588DE293561C49720101
588DE293561C49720102
588DE63A4FC943220102
588DE63A4FC943220103
588DE9960ECB47320104
How i want the data layout:
Cust_ID Jan-10Feb-10Mar-10Apr-10
588DE245D9494A81111
588DE252CD6F44C0010
588DE293561C4971100
588DE63A4FC94320110
588DE9960ECB4730001
Any ideas on this one? Is it as simple as writing a new query? i have millions of customer records by the way.
Sorry if i've not given enough information just let me know what else i need to give. FYI i'm using SQL Server Management studio 2005 without any of the add ons like Business Intelligence.
Cheers
Dan
October 6, 2010 at 7:21 am
-- *** Test data in consumable format ***
-- You will get quicker results if you provide this
CREATE TABLE #t
(
Cust_ID char(15) NOT NULL
,[Year] smallint NOT NULL
,[Month] tinyint NOT NULL
)
INSERT INTO #t
SELECT '588DE245D9494A8', 2010, 1
UNION ALL SELECT '588DE245D9494A8', 2010, 2
UNION ALL SELECT '588DE245D9494A8', 2010, 3
UNION ALL SELECT '588DE245D9494A8', 2010, 4
UNION ALL SELECT '588DE252CD6F44C', 2010, 3
UNION ALL SELECT '588DE293561C497', 2010, 1
UNION ALL SELECT '588DE293561C497', 2010, 2
UNION ALL SELECT '588DE63A4FC9432', 2010, 2
UNION ALL SELECT '588DE63A4FC9432', 2010, 3
UNION ALL SELECT '588DE9960ECB473', 2010, 4
-- *** End Test data in consumable format ***
SELECT Cust_ID
,SUM(CASE WHEN [Year] = 2010 AND [Month] = 1 THEN 1 ELSE 0 END) AS [Jan-10]
,SUM(CASE WHEN [Year] = 2010 AND [Month] = 2 THEN 1 ELSE 0 END) AS [Feb-10]
,SUM(CASE WHEN [Year] = 2010 AND [Month] = 3 THEN 1 ELSE 0 END) AS [Mar-10]
,SUM(CASE WHEN [Year] = 2010 AND [Month] = 4 THEN 1 ELSE 0 END) AS [Apr-10]
FROM #t
GROUP BY Cust_ID
October 6, 2010 at 8:19 am
Wow that is perfect thanks very much 🙂
October 20, 2010 at 8:44 pm
happyfax, is this going to be a reporting table? If so, then there are sometimes benefits to de-normalizing a table. However, if it is because someone told you that is the way they want it, then you should be aware it is easier to pivot data in a normalized table than it is to un-pivot data in a de-normalized table, so you should be aware of what you are going to have to do with the data in the future, and have a plan to deal with it. Just a thought.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply