February 17, 2008 at 6:43 am
Hi folks,
I have a SalesDetails table that includes more than one million rows.I would like to list the distinct sales prices of items in this table as a report.I will create another table to list this results that consists 5 different Sales Price columns.How can I do this? Please help.
I know that this is a cross-tab report query but unfortunately I could not achieve to develeop this SQL code.
🙁
February 17, 2008 at 7:35 am
Hi Again,
Finally I have found a stored procedure that dynamically creates a crosstab report.SP is like this:
CREATE procedure sp_CrossTab
@tablename varchar(255), -- Table/View on which to perform the cross tab query.
@crosscolumn varchar(255), -- Attribute to be used as columns in the cross tab.
@crossrow varchar(255), -- Attribute to be used as rows in the cross tab.
@crossvalue varchar(255) -- Attribute to be used as value in the cross tab.
As
-- Work variables
declare
@sql varchar(8000), -- Hold the dynamically created sql statement
@colname varchar(255), -- The current column when building sql statement
@i smallint, -- know when we reached the last column (@i = @cols)
@cols smallint, -- Number of columns
@longest_col smallint, -- the len() of the widest column
@CrLf char(2)
-- Constants
declare
@max_cols_in_table smallint,
@max_col_name_len smallint,
@max_statement_len smallint,
-- @sql7 bit, -- 1 when version 7, 0 otherwise.
@err_severity int
set nocount on
set @max_cols_in_table = 255
set @max_statement_len = 8000
set @max_col_name_len = 128
set @err_severity = 11
set @CrLf = char(13) + char(10)
-- Check inputs
if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
raiserror ('Missing parameter(s)!',@err_severity,1)
return 0
end
-- Check for existence of the table.
if (not exists(select * from sysobjects where name like @tablename))begin
raiserror ('Table/View for crosstab not found!',@err_severity,1)
return 0
end
-- Don't check for columns because we may actually get an expression as the column name
-- prepare for future feature of checking database version to validate
-- inputs. Default to version 7
--set @sql7 = 1
--if (patindex('%SQL Server 7.%',@@version) = 0) begin
-- set @sql7 = 0
--end
-- Extract all values from the rows of the attribute
-- we want to use to create the cross column. This table
-- will contain one row for each column in the crosstab.
create table #crosscol (crosscolumn varchar(255))
set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn +
' From ' + @tablename --+
--' Group By ' + @crosscolumn
--print @sql
exec (@sql)
set @cols = @@rowcount
if @cols > @max_cols_in_table begin
raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1)
return 0
end
else begin
if @cols = 0 begin
raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
return 0
end
else begin
-- Check if any of the data is too long to make it a name of a column
select @longest_col = max(len(convert(varchar(129),crosscolumn)))
from #crosscol
if @longest_col > @max_col_name_len begin
raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
return 0
end
else begin
-- All Validations OK, start building the dynamic sql statement
set @sql = ''
-- Use tmp table rows to create the sql statement for the crosstab.
-- each row in the table will be a column in the cross-tab
set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As '
+ @crossrow + ', ' + @CrLf + space(4)
--set @sql = 'select ' + @crossrow + ', ' + char(13)
declare cross_sql cursor for
select crosscolumn
from #crosscol
order by crosscolumn
--print 'Sql cross statment: ' + @sql
open cross_sql
fetch next from cross_sql into @colname
-- Use "@i" to check for the last column. We need to input commas
-- between columns, but not after the last column
set @i = 0
while @@FETCH_STATUS = 0 begin
set @i = @i + 1
set @colname = isnull(@colname,'Undefined')
set @crossvalue = isnull(@crossvalue, 0)
Set @sql = @sql + '''' +
convert(varchar(128), @colname) +
''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
+ char(13) + char(10) + space(8) +
' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '
if @i < @cols
set @sql = @sql + ', ' + @CrLf + space(4)
else
set @sql = @sql + @CrLf
fetch next from cross_sql into @colname
end
close cross_sql
deallocate cross_sql
set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
if len(@sql) >= @max_statement_len begin
raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
return 0
end
exec (@sql)
Select 'Sql' = @sql
set nocount off
return 1
end
end
end
GO
----------------
But my purpose is create a static table that holds just 5 distinc sales price changes.This sp creates all distinct sales prices because of all stock codes.
PLEASE HELP
February 17, 2008 at 7:59 am
C'mon... I know you're in a pinch but stop and think about it... we don't know what your SalesDetails detail table looks like, we know nothing about the data in it, you haven't defined what the report table looks like, and you haven't defined what summary information you want in the table.
Start at the following URL... It's EXTREMELY URGENT that you read it and understand it. 😉
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
February 17, 2008 at 8:03 am
And I would get your reporting tool to do the cross-tab rather than trying to bend a SQL resultset to do it. For example, both Crystal Reports and SQL Server Reporting Services can easily do cross-tabs or matrices.
A cross-tab is a display/presentation method. Use SQL to return data and then let your display package format it.
February 17, 2008 at 8:15 am
Thank you very much for your replies...At least, you replied this question 🙂
My final data report should include just six columns:
StockCode
SalesPrice1
SalesPrice2
.
.
SalesPrice5
That's all..
February 17, 2008 at 8:40 am
erdem (2/17/2008)
Thank you very much for your replies...At least, you replied this question 🙂My final data report should include just six columns:
StockCode
SalesPrice1
SalesPrice2
.
.
SalesPrice5
That's all..
But, you didn't read my previous reply... you've still not provided enough information for us to help you.
--Jeff Moden
February 17, 2008 at 8:49 am
Ok...You are exactly right...
I have attached a text file.This is my SELECT query results.
My purpose is to create another table that holds Sales Price value changes..(I will follow just 5 price changes).
Clear?
February 17, 2008 at 9:03 am
Yeaah... I'm going to spend my time reformatting your data...
This is what your text file looks like...
SalesDate StockCode costPrice SalesPrice SalesAmount Total ----------------------- -------------------------------------------------- ----------- ----------- ----------- ----------- 2008-01-01 00:00:00 A 10 20 5 100 2008-01-02 00:00:00 A 10 25 4 100 2008-01-05 00:00:00 A 10 50 2 100 2008-01-08 00:00:00 A 10 15 10 150 2008-01-10 00:00:00 A 5 5 50 250 2008-01-02 00:00:00 B 50 100 5 500 2008-01-03 00:00:00 B 50 125 4 500 2008-01-04 00:00:00 B 50 50 20 1000 2008-01-05 00:00:00 B 50 50 10 500 (9 row(s) affected)
When you're ready to post the CREATE statement for both tables and provide some test data in the form of INSERT/SELECT's or INSERT/VALUES, then we'll have enough information to help. Not trying to be difficult here... I'm just not a mind reader...
I'm all done here... good luck.
--Jeff Moden
February 17, 2008 at 9:05 am
Thank you
🙁
February 17, 2008 at 9:25 am
USE [TEST]
GO
/****** Object: Table [dbo].[tblSales] Script Date: 02/17/2008 18:13:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSales](
[SalesDate] [smalldatetime] NULL,
[StockCode] [nvarchar](50) COLLATE Turkish_CI_AS NULL,
[costPrice] [int] NULL,
[SalesPrice] [int] NULL,
[SalesAmount] [int] NULL,
[Total] [int] NULL
) ON [PRIMARY]
------
Here is the sample data..
SELECT '2008-01-01','A','10','20','5','100' UNION ALL
SELECT '2008-01-02','A','10','25','4','100' UNION ALL
SELECT '2008-01-05','A','10','25','2','100' UNION ALL
SELECT '2008-01-08','A','5','50','10','150' UNION ALL
SELECT '2008-01-10','A','50','25','50','250' UNION ALL
SELECT '2008-01-02','B','50','5','5','500' UNION ALL
SELECT '2008-01-03','B','50','50','4','500' UNION ALL
SELECT '2008-01-04','B','50','20','10','250' UNION ALL
February 17, 2008 at 9:54 am
Now, we're cookin'... I'll be right back... 😉
--Jeff Moden
February 17, 2008 at 10:17 am
Ta-da! See how much easier that is for folks to work with. Thanks for taking the time to help us help you...
Here's the solution including the test setup you were kind enough to provide...
--===== Identify a safe database to use for the test
USE TempDB
--===== Create the test table. This is NOT part of the solution
IF OBJECT_ID('TempDB.dbo.tblSales','U') IS NOT NULL
DROP TABLE dbo.tblSales
CREATE TABLE [dbo].[tblSales]
(
[SalesDate] [smalldatetime] NULL,
[StockCode] [nvarchar](50) COLLATE Turkish_CI_AS NULL,
[costPrice] [int] NULL,
[SalesPrice] [int] NULL,
[SalesAmount] [int] NULL,
[Total] [int] NULL
)
--===== Put some test data in the test table.
-- This is NOT part of the solution
INSERT INTO dbo.tblsales
SELECT '2008-01-01','A','10','20','5','100' UNION ALL
SELECT '2008-01-02','A','10','25','4','100' UNION ALL
SELECT '2008-01-05','A','10','25','2','100' UNION ALL
SELECT '2008-01-08','A','5','50','10','150' UNION ALL
SELECT '2008-01-10','A','50','25','50','250' UNION ALL
SELECT '2008-01-02','B','50','5','5','500' UNION ALL
SELECT '2008-01-03','B','50','50','4','500' UNION ALL
SELECT '2008-01-04','B','50','20','10','250'
--===== Demo the solution
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
SELECT --Reassemble the data in the desire form using a cross tab
d.StockCode,
SalesPrice1 = MAX(CASE WHEN PriceNum = 1 THEN d.SalesPrice ELSE NULL END),
SalesPrice2 = MAX(CASE WHEN PriceNum = 2 THEN d.SalesPrice ELSE NULL END),
SalesPrice3 = MAX(CASE WHEN PriceNum = 3 THEN d.SalesPrice ELSE NULL END),
SalesPrice4 = MAX(CASE WHEN PriceNum = 4 THEN d.SalesPrice ELSE NULL END),
SalesPrice5 = MAX(CASE WHEN PriceNum = 5 THEN d.SalesPrice ELSE NULL END)
FROM (--==== Derived table "d" numbers each price by stock code
SELECT StockCode,
SalesPrice,
PriceNum = (SELECT COUNT(*)
FROM dbo.tblSales s2
WHERE s2.StockCode = s1.StockCode
AND s2.SalesDate<=s1.SalesDate)
FROM dbo.tblSales s1
)d
GROUP BY d.StockCode
ORDER BY d.StockCode
... and here's what the output looks like...
StockCode SalesPrice1 SalesPrice2 SalesPrice3 SalesPrice4 SalesPrice5
-------------------------------------------------- ----------- ----------- ----------- ----------- -----------
A 20 25 25 50 25
B 5 50 20 NULL NULL
Now, I'll also tell you this... I've built nothing into this to make sure you don't have more than 5 prices. I also see nothing in your table that resembles a primary key so there's nothing to prevent dupes even on the same day. If those are missing requirements, then you need to come back with what the requirements should be...
--Jeff Moden
February 17, 2008 at 10:22 am
Oh yeah... for those in the know... yes, I used a triangular join in a correlated sub-query... performance will be terrible compared to the "UPDATE" method I wrote about, but the group sizes are small enough where most folks won't notice. At the worst case, this will generate 15 million internal rows on the million row table if all 5 prices are available for each product.
--Jeff Moden
February 17, 2008 at 10:49 am
Thank you very much...It work perfect...
🙂
Good cooking....)
February 17, 2008 at 10:59 am
Thanks for the feedback and glad it suits your needs... now you know why I kept asking for more detail...
Folks, this is a perfect example of why I wrote the following article...
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
... and it works in real life, as well. If you don't describe the problem or don't describe the problem adequately, there's just no way to get a correct answer.
Help us help you... read the article I wrote and follow the advice in it. It'll take you a little longer to post the information but, overall, it'll take a lot shorter to get the answer you were looking for.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply