Need help in this query

Question

Need help in this query

sql.abhishek

Old Hand

Points: 381
More actions
September 15, 2010 at 2:30 pm

#240487

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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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.

- Craig Farrell

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

WayneS SSC Guru Points: 95460 More actions · Answer 2

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

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. 🙂

- Craig Farrell

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

LutzM SSC Guru Points: 107049 More actions · Answer 4

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! 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sql.abhishek Old Hand Points: 381 More actions · Answer 5

@ 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...

WayneS SSC Guru Points: 95460 More actions · Answer 6

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 7

sql.abhishek (9/15/2010)
@ 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...

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

sql.abhishek (9/15/2010)
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.

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 10

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

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Chama SSC Enthusiast Points: 198 More actions · Answer 11

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...

LutzM SSC Guru Points: 107049 More actions · Answer 12

... 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