April 28, 2008 at 1:11 pm
I am using SQL 2005.
I have a tricky problem here.
I have a table which contains data in the following format.
description1 description2 value1
description1 description2 value2
description1 description2 value3
description1 description2 value4
description6 description7 value3
description6 description7 value4
Now I need to produce a new table using the above table in the following format
description1 description2 value1 value2 value3 value4
description6 description7 value3 value4 0 0
i.e making 4 rows of data from old into a single row of new table. Here , for example description1 and description2 are used as a criteria for determining this new row.
Now I use this new table , to be used in asp.net.but since it takes long time.Its not acceptable to the users.
I can easily solve this problem using SQL cursors. But my problem is with cursors, the new table takes a vey long time to be generated, since the old table has lots of data.
I wanted to know if there is a better way to solve this problem other than using cursors ?
April 28, 2008 at 1:19 pm
Please post your CREATE TABLE statement and some sample data (in the form of an INSERT statement) so we can help you more effectively. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2008 at 1:46 pm
I already have the below mentioned base table populated with data.
Create table booksdata(
date_enrolled [datetime],
book_name varchar(50),
author_name varchar(100),
book_visited float
)
The data in this table is like,
date_enrolled book_name author_name book_visited
04/12/2008 xyz Henry 4
04/13/2008 xyz Henry 5
04/14/2008 xyz Henry 2
04/15/2008 xyz Henry 56
04/16/2008 xyz Henry 1
04/12/2008 erg David 9
04/13/2008 erg David 67
04/14/2008 erg David 09
04/15/2008 erg David 34
04/16/2008 erg David 98
Now I create a temp table with this format
Create #temp(
book_name varchar(50),
author_name varchar(100)
)
Now I dynamically add columns to this temp table depending on the number of days the user has selected i.e.(date_enrolled)
so the #temp structure now becomes if the user selects 4 days
book_name author_name 04/12/2008 04/13/2008 04/14/2008 04/15/2008
Now , My problem is populating this #temp table using booksdata table, It should basically look like as showed below
book_name author_name 04/12/2008 04/13/2008 04/14/2008 04/15/2008
xyz Henry 4 5 2 56
erg David 9 67 09 34
My problem is populating this #temp table. I can always do this using cursors. But it slows down tremendously.
I want to know if there is any other way ?
I appreciate your help
April 28, 2008 at 1:56 pm
Can there be more than one book_visted for one enrolled_date, or is that a count? Is there a maximum number of days they can select?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2008 at 2:05 pm
there can be only 1 enrolled_date for book_visited.
Book_visited is a value.
However, for an author and book_name there can be many enrolled_date and book_visited.
I determine the new row of #temp table based on the author and the book_name
They can select any number of days. from 1 to 30 days
April 28, 2008 at 2:11 pm
This is really a crosstab report and the new PIVOT command can help here. I'd look that up and try a few possibilities with your table.
April 28, 2008 at 2:21 pm
Set up test data ...
DECLARE @booksdata TABLE
(date_enrolled [datetime],
book_name varchar(50),
author_name varchar(100),
book_visited float)
INSERT INTO @booksdata
SELECT '04/12/2008','xyz','Henry', 4 UNION
SELECT '04/13/2008','xyz','Henry', 5 UNION
SELECT '04/14/2008','xyz','Henry', 2 UNION
SELECT '04/15/2008','xyz','Henry', 56 UNION
SELECT '04/16/2008','xyz','Henry', 1 UNION
SELECT '04/12/2008','erg','David', 9 UNION
SELECT '04/13/2008','erg','David', 67 UNION
SELECT '04/14/2008','erg','David', 09 UNION
SELECT '04/15/2008','erg','David', 34 UNION
SELECT '04/16/2008','erg','David' , 98;
Use a Common Table Expression (CTE) to strip out the daynumbers. I'm using the number of days away from a supplied value. Then pivot on that.
DECLARE @reportDate DATETIME
SELECT @reportDate = '4/16/2008';
WITH mData
AS (SELECT
DATEDIFF(DAY,date_enrolled,@reportDate) AS dayNumber,
book_name,
author_name,
book_visited
FROM @booksdata) --SELECT * FROM mData
SELECT
book_name, author_name
,[1] ,[2], [3], [4], [5], [6] ,[7], [8], [9], [10]
,[11] ,[12], [13], [14], [15], [16] ,[17], [18], [19], [20]
,[21] ,[22], [23], [24], [25], [26] ,[27], [28], [29], [30]
FROM
(SELECT
dayNumber,
book_name,
author_name,
book_visited
FROM mData) AS p
PIVOT
(SUM(book_visited) FOR dayNumber IN ([1] ,[2], [3], [4], [5], [6] ,[7], [8], [9], [10],
[11] ,[12], [13], [14], [15], [16] ,[17], [18], [19], [20],
[21] ,[22], [23], [24], [25], [26] ,[27], [28], [29], [30])) AS pvt
The obvious difference from your requested results are the column names not being actual dates. You'd need to write some dynamic SQL for that. Or whatever you're using on the presentation level could add the column number(name) to the supplied date and display the result.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2008 at 2:24 pm
OOPS, you need to add in a zero, for the date selected to show up...
WITH mData
AS (SELECT
DATEDIFF(DAY,date_enrolled,'4/16/2008') AS dayNumber,
book_name,
author_name,
book_visited
FROM @booksdata) --SELECT * FROM mData
SELECT
book_name, author_name
,[0] ,[1] ,[2], [3], [4], [5], [6] ,[7], [8], [9], [10]
,[11] ,[12], [13], [14], [15], [16] ,[17], [18], [19], [20]
,[21] ,[22], [23], [24], [25], [26] ,[27], [28], [29], [30]
FROM
(SELECT
dayNumber,
book_name,
author_name,
book_visited
FROM mData) AS p
PIVOT
(SUM(book_visited) FOR dayNumber IN ([0], [1] ,[2], [3], [4], [5], [6] ,[7], [8], [9], [10],
[11] ,[12], [13], [14], [15], [16] ,[17], [18], [19], [20],
[21] ,[22], [23], [24], [25], [26] ,[27], [28], [29], [30])) AS pvt
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2008 at 2:48 pm
Thanks Jason , I'll try out your suggestion and get back with you
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply