September 15, 2010 at 2:30 pm
hi
i have my input table as follows
ID----total_amt-----total_month--------start_date
1-----1200000--------6-------------- 9/4/2009
2----300000---------3---------------- 1/1/2006
3----300000---------10--------------- 9/6/2006
4
5 ...so on
and i want a way to get the output as
ID
-----sept'09----oct'09----nov'09-----dec'09-----total for 2009---jan'10-----feb10----Tot
1---200000---200000---200000---200000----- 800000------ 200000----200000--
-----jan'06----- feb'06-----mar'06-----Total for 2006
2-----100000----100000-----100000----300000
divide the amount as per the total number of months
I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.
September 15, 2010 at 2:33 pm
sql.abhishek (9/15/2010)
I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.
For the code, you'll really need to post the DDL of the tables involved, and some insert statements for the sample data.
For the general outline, you're looking at some date functions to group the data to month/year, an aggregation using a group by, and a pivot to send the data sideways.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 2:42 pm
Craig Farrell (9/15/2010)
sql.abhishek (9/15/2010)
I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.For the code, you'll really need to post the DDL of the tables involved, and some insert statements for the sample data.
For the general outline, you're looking at some date functions to group the data to month/year, an aggregation using a group by, and a pivot to send the data sideways.
For how to get the DDL/insert code, please read the first two links in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 2:47 pm
WayneS (9/15/2010)
Craig Farrell (9/15/2010)
sql.abhishek (9/15/2010)
I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.For the code, you'll really need to post the DDL of the tables involved, and some insert statements for the sample data.
For the general outline, you're looking at some date functions to group the data to month/year, an aggregation using a group by, and a pivot to send the data sideways.
For how to get the DDL/insert code, please read the first two links in my signature.
I give in. Wayne, I'm stealin' yer links. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 2:54 pm
The requested output is not really something you should expect from a SQL statement:
a) It seems like you want to have "flexible column names" for each row which is totally against any kind of relational database concept. A table has a column with a name and a value per row.
b) It also seems you're looking for a flexible number of columns holding the same type of information (e.g. Total for ID) in different columns depending on the value of the original column total_month.
You might want to rethink the requirement you're looking for. To split data in the way you describe shouldn't be done using T-SQL. It's a front end task. What you could do on the SQL side is a query to return ID, column number, column name and column value. At the front end, design a layout to get the column name and related column value per ID ordered by column number.
It is possible to do it using T-SQL (even including a blank row between each statement). But that would be like banging a nail into a wall with a screwdriver: possible, but not recommended. Get the right tool for the job! 😉
September 15, 2010 at 3:01 pm
@ WayneS
Thanks a lot for the links ... I really appreciate ur help ..
i think i can start working on these lines and i may get the desired results.
Regards...
September 15, 2010 at 3:16 pm
Craig Farrell (9/15/2010)
I give in. Wayne, I'm stealin' yer links. 🙂
No problem. We all have them because it's just so much easier on us...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 3:17 pm
sql.abhishek (9/15/2010)
@ WayneSThanks a lot for the links ... I really appreciate ur help ..
i think i can start working on these lines and i may get the desired results.
Regards...
You might want to also check out the links in my signature for CROSS-TABS/Pivots, Part 1 and Part 2.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 7:14 pm
sql.abhishek (9/15/2010)
hii have my input table as follows
ID----total_amt-----total_month--------start_date
1-----1200000--------6-------------- 9/4/2009
2----300000---------3---------------- 1/1/2006
3----300000---------10--------------- 9/6/2006
4
5 ...so on
and i want a way to get the output as
ID
-----sept'09----oct'09----nov'09-----dec'09-----total for 2009---jan'10-----feb10----Tot
1---200000---200000---200000---200000----- 800000------ 200000----200000--
-----jan'06----- feb'06-----mar'06-----Total for 2006
2-----100000----100000-----100000----300000
divide the amount as per the total number of months
I am desperately looking for some piece of code to get this type of out put .... kindly help me out of this.
The others are correct about the links on how to provide data. That, notwithstanding, please see the following link for a coded example of what you want to do. Wayne points to the same link, as well. http://www.sqlservercentral.com/Forums/Topic986540-391-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2010 at 7:16 pm
LutzM (9/15/2010)
The requested output is not really something you should expect from a SQL statement:a) It seems like you want to have "flexible column names" for each row which is totally against any kind of relational database concept. A table has a column with a name and a value per row.
b) It also seems you're looking for a flexible number of columns holding the same type of information (e.g. Total for ID) in different columns depending on the value of the original column total_month.
You might want to rethink the requirement you're looking for. To split data in the way you describe shouldn't be done using T-SQL. It's a front end task. What you could do on the SQL side is a query to return ID, column number, column name and column value. At the front end, design a layout to get the column name and related column value per ID ordered by column number.
It is possible to do it using T-SQL (even including a blank row between each statement). But that would be like banging a nail into a wall with a screwdriver: possible, but not recommended. Get the right tool for the job! 😉
I absolutely agree with what you say but consider the following... what if there is no "front end" for this task?? 😉 Remember... to a nail, everything is a hammer. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2010 at 1:20 pm
Jeff Moden (9/15/2010)
LutzM (9/15/2010)
...I absolutely agree with what you say but consider the following... what if there is no "front end" for this task?? 😉 Remember... to a nail, everything is a hammer. 😛
Assuming I'd be forced to come up with a result in a format as requested but without any front end app, I'd probably do it this way (for a static solution and as a starting point to have something to turn into dynamic sql)
DECLARE @tbl TABLE
(
ID INT,
total_amt INT,
total_month INT,
start_date DATETIME
)
INSERT INTO @tbl
SELECT 1,1200000,6,'9/14/2009' UNION ALL
SELECT 2, 300000,3,'1/1/2006' UNION ALL
SELECT 3, 300000,10,'9/6/2006'
;
WITH cte AS
(
SELECT
ID,
N+1 AS Pos,
STUFF(CONVERT(CHAR(11),DATEADD(mm,N,start_date),0),4,6,'''') AS col1,
total_amt/total_month AS amt_mnth,
total_amt
FROM @tbl t
CROSS APPLY
(
SELECT number N
FROM master..spt_values
WHERE TYPE='P'
AND number < total_month
)x
)
SELECT
CASE WHEN grp=3 THEN '' ELSE CAST(ID AS VARCHAR(10)) END AS ID_,
-- one sample row to show the nest level of the CASE statements
MAX(
CASE
WHEN Pos=1 THEN
CASE
WHEN grp= 1 THEN col1
WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10))
ELSE ''
END
ELSE ''
END) AS [ ],
MAX(CASE WHEN Pos=2 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=3 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=4 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=5 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=6 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=7 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=8 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=9 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=10 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ]
FROM cte
CROSS APPLY
(
SELECT 1 AS grp UNION ALL -- display the month
SELECT 2 UNION ALL -- display the value per month
SELECT 3 -- empty row
) y
GROUP BY ID,grp
ORDER BY ID,grp
/* result set
ID
1Sep'09Oct'09Nov'09Dec'09Jan'10Feb'10
1200000200000200000200000200000200000
2Jan'06Feb'06Mar'06
2100000100000100000
3Sep'06Oct'06Nov'06Dec'06Jan'07Feb'07Mar'07Apr'07May'07Jun'07
330000300003000030000300003000030000300003000030000
*/
September 16, 2010 at 1:49 pm
You can make if stamment
IF (SELECT COUNT(Months) FROM tablename
after
declare the amount of each months (you make specific that some months are different amounts)
begin and end store procedure...should be work fine...
September 16, 2010 at 2:07 pm
... and here's the version to include the total amount right after the last month, based on the sample posted before.
;
WITH cte AS
(
SELECT
ID,
N+1 AS Pos,
STUFF(CONVERT(CHAR(11),DATEADD(mm,N,start_date),0),4,6,'''') AS col1,
total_amt/total_month AS amt_mnth
FROM @tbl t
CROSS APPLY
(
SELECT number N
FROM master..spt_values
WHERE TYPE='P'
AND number < total_month
)x
UNION ALL
SELECT
ID,
total_month + 1 AS Pos,
'total' AS col1,
total_amt AS amt_mnth
FROM @tbl t
)
SELECT
CASE WHEN grp=3 THEN '' ELSE CAST(ID AS VARCHAR(10)) END AS ID_,
-- one sample row to show the nest level of the CASE statements
MAX(
CASE
WHEN Pos=1 THEN
CASE
WHEN grp= 1 THEN col1
WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10))
ELSE ''
END
ELSE ''
END) AS [ ],
MAX(CASE WHEN Pos=2 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=3 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=4 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=5 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=6 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=7 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=8 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=9 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=10 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ],
MAX(CASE WHEN Pos=11 THEN CASE WHEN grp= 1 THEN col1 WHEN grp= 2 THEN CAST(amt_mnth AS VARCHAR(10)) ELSE '' END ELSE '' END) AS [ ]
FROM cte
CROSS APPLY
(
SELECT 1 AS grp UNION ALL -- display the month
SELECT 2 UNION ALL -- display the value per month
SELECT 3 -- empty row
) y
GROUP BY ID,grp
ORDER BY ID,grp
September 19, 2010 at 1:49 pm
@sql.abhishek,
So! Are you all set or what? If you're all set, would you mind posting your final solution so that others may learn, please? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 10:08 am
Hey Lutz,
Thanx alot for the solution...
I am trying to put my exact problem into your solution and will see how it works ......
Regards,
abhisheka
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply