question

ranjanrkl avatar image
ranjanrkl asked

how to insert into multiple columns from one column in sql

suppose i have a column called [SpendPlanned] and [SpendGME] with data corresponding to its monthid column in such a fashion below say in 'x' table: i have only two columns[SpendPlanned] and [SpendGME] with all the data and wanted to insert these data separately in different columns belonging to its Monthid in another table via stored procedure ![enter image description here][1] i want to insert these values belonging to its monthid in its corresponding month column which is shown below in 'Y' table: ![enter image description here][2] [1]: http://i.stack.imgur.com/r7vxa.png [2]: http://i.stack.imgur.com/V9oLA.png like data of SpendPlanned- '500' for monthid '201401' from 'X' table will be inserted to Jan Planned column of 'Y' Table, similarly data of Spend GME- '500' for MonthID 201401 will be inserted to Jan GME column of 'Y' table and so on for other months column in 'Y' table will be inserted from 'X' table. i want to write a stored procedure or any simple query which will fulfil the above requirement. any help on this would be really appreciated. Thanks, Ranjan.
sql-server-2012stored-procedurestables
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sdoubleday avatar image
sdoubleday answered
I think you are looking for the PIVOT keyword [ http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx][1]. IF OBJECT_ID('tempdb..#myTest') IS NOT NULL DROP TABLE #myTest; CREATE TABLE #myTest ( ID INT , CalendarYearAndMonthNumber INT , Planned INT , GME INT ); INSERT INTO #myTest VALUES ( 1, 201401, ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())) ) , ( 2, 201401, ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())) ) , ( 1, 201402, ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())) ) , ( 2, 201402, ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())) ) , ( 1, 201403, ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())) ) , ( 2, 201403, ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())) ); ; WITH myCTE AS ( SELECT * , DATENAME(MONTH, CAST(CAST(CalendarYearAndMonthNumber AS VARCHAR(25)) + '01' AS DATE)) AS MonthName /*To pivot this data, we will need column headers. These will become our column headers.*/ , DATENAME(MONTH, CAST(CAST(CalendarYearAndMonthNumber AS VARCHAR(25)) + '01' AS DATE)) + ' Planned' AS Label_p , DATENAME(MONTH, CAST(CAST(CalendarYearAndMonthNumber AS VARCHAR(25)) + '01' AS DATE)) + ' GME' AS Label_g FROM #myTest ) SELECT * FROM ( /*Pivot statement*/ SELECT ID , [January Planned] , [February Planned] , [March Planned] FROM ( SELECT ID , planned , Label_p FROM myCTE /*This subselect is important, because if you have extra columns here you will wind up with extra rows in your output*/ ) AS mySource PIVOT ( /*In this case, we are actually only summing one row*/ SUM(Planned) /*The column to be turned into headers*/ FOR Label_p IN ( /*List all of the unique values to be displayed as new columns*/ [January Planned], [February Planned], [March Planned] ) ) as myPivot ) AS myPlanned LEFT OUTER JOIN ( SELECT * FROM ( SELECT ID , GME , Label_g FROM myCTE ) AS mySource PIVOT ( SUM(gme) FOR Label_g IN ( [January GME], [February GME], [March GME] ) ) as myPivot ) AS myGME ON myplanned.id = mygme.id [1]: http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for this..
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Whilst it is correct that you need to pivot the data, and you can certainly use the `PIVOT` functionality, it can sometimes be easier to construct, read and maintain a 'manual' pivot {personal choice here :)} Given that there are a finite number of months, i.e 12 and 2 values, then that's 24 calculations to write out - you might think that's overkill, but personally I like it. declare @YourTable table ( Year_ID int, MonthID int, SpendPlanned int, SpendGME int ) insert into @YourTable select 1,201401,500,500 insert into @YourTable select 2,201401,7000,7000 insert into @YourTable select 1,201402,1000,1000 insert into @YourTable select 2,201402,7500,7500 insert into @YourTable select 2,201403,2000,2000 insert into @YourTable select 1,201403,8000,8000 insert into @YourTable select 2,201404,2500,2500 insert into @YourTable select 1,201404,8500,8500 insert into @YourTable select 2,201405,3000,3000 insert into @YourTable select 1,201405,9000,9000 select Year_ID, sum(case when MonthID = 201401 then SpendPlanned else 0 end) as JanPlanned, sum(case when MonthID = 201401 then SpendGME else 0 end) as JanGME, sum(case when MonthID = 201402 then SpendPlanned else 0 end) as FebPlanned, sum(case when MonthID = 201402 then SpendGME else 0 end) as FebGME, sum(case when MonthID = 201403 then SpendPlanned else 0 end) as MarPlanned, sum(case when MonthID = 201403 then SpendGME else 0 end) as MarGME, sum(case when MonthID = 201404 then SpendPlanned else 0 end) as AprPlanned, sum(case when MonthID = 201404 then SpendGME else 0 end) as AprGME, sum(case when MonthID = 201405 then SpendPlanned else 0 end) as MayPlanned, sum(case when MonthID = 201405 then SpendGME else 0 end) as MayGME /*...etc....*/ from @YourTable group by Year_ID Further carving up the MonthID makes this easy to support multiple years worth of data.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for this Kev.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.