Cross Tab Question
-
Hello,
I have a cross tab situation in which I need to convert a column with date values in it (yyyy-mm) so that the values become the columns.
The problem is, I need a new column for each new yyyy-mm that is introduced into the table. I don't have a fixed set of values.
How might I go about approaching this? If you need more information, please let me know.
I do not have Analysis Services available to me for this.
Thank you for your help!
CSDunn
-
I have done this onec in past but I don't know if thats suitable for you.
Use Dynamic SQL to keep adding new columns to the table:
For expample if you have a table called Reports(CustomerID,[2006-Jan],[2006-Feb],[2006-Mar]..) and so soon I get sales for the month thats not in syscolumns the Dynamic SQL will add the new Column.
Alter Table Reports add [2006-Sept] datatype.
Then as part of daily update the data for relavent columns.
Thanks
Sreejith
-
CS,
Post the table schema and some example data (hopefully in the form of INSERTs) and I'll give you a dog'n'pony...
--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) -
Dog'n'pony? Must be a colloquialism.
BTW, we must stop meeting like this
Dave J
-
CS,
I've a sample sp which may help you....
CREATE PROCEDURE [DBO].[CrossTab]
@Select VarChar(8000),
@SumFunc VarChar(1000),
@Pivot VarChar(100),
@Table VarChar(100)
AS
DECLARE @Delim VarChar(1)
DECLARE @mSQL1 VarChar(8000), @mSQL2 VarChar(8000), @mSQL3 VarChar(8000)
DECLARE @mTmp VarChar(1000), @mTmp1 VarChar(1000), @mTmp2 VarChar(1000)
DECLARE @TLen Int
SET NOCOUNT OFF
SET ANSI_WARNINGS OFF
IF EXISTS(SELECT * FROM DBO.SysObjects WHERE ID = OBJECT_ID(N'[DBO].[PIVOT]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [DBO].[PIVOT]
EXEC ('SELECT ' + @Pivot + ' AS PIVOT INTO [DBO].PIVOT FROM ' + @Table + ' WHERE 1=2')
EXEC ('INSERT INTO PIVOT SELECT DISTINCT ' + @Pivot + ' FROM ' + @Table + ' WHERE ' + @Pivot + ' Is Not Null')
IF @@ROWCOUNT <=0
BEGIN
DROP TABLE [DBO].[PIVOT]
GOTO ExitProcess
END
SELECT @SumFunc = Stuff(@SumFunc, Len(@SumFunc), 1, ' END)')
SELECT @Delim = (CASE IsNumeric(Pivot) WHEN 1 THEN '''' ELSE '''' END) FROM PIVOT
SET @mTmp = ''
SET @mSQL1 = ''
SET @mSQL2 = ''
SET @mSQL3 = ''
SET @TLen = 0
SELECT @mTmp = ',''' + Convert(VarChar(100), Pivot) + ''' = ' + Stuff(@SumFunc, CharIndex('(', @SumFunc) + 1, 0, 'CASE ' + @Pivot + ' WHEN ' + @Delim +
Convert(VarChar(100), Pivot) + @Delim + ' THEN ' ),
@TLen = @TLen + Len(@mTmp),
@mSQL1 = (CASE WHEN @TLen < 8000 THEN @mSQL1 + @mTmp ELSE @mSQL1 END),
@mSQL2 = (CASE WHEN @TLen >= 8000 AND @TLen < 15999 THEN @mSQL2 + @mTmp ELSE @mSQL2 END),
@mSQL3 = (CASE WHEN @TLen >= 16000 AND @TLen < 24000 THEN @mSQL3 + @mTmp ELSE @mSQL3 END)
FROM PIVOT
ORDER BY PIVOT
IF EXISTS(SELECT * FROM DBO.SysObjects WHERE ID = OBJECT_ID(N'[DBO].[PIVOT]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [DBO].[PIVOT]
SET @mTmp1 = LEFT(@Select, CharIndex(' FROM ', @Select))
IF Len(@Select) - CharIndex(' FROM ', @Select) + 1 > 0
BEGIN
SET @mTmp2 = RIGHT(@Select, Len(@Select) - CharIndex(' FROM ', @Select) + 1)
--select (@mTmp1 + @mSQL1 + @mSQL2 + @mSQL3 + @mTmp2)
EXEC (@mTmp1 + @mSQL1 + @mSQL2 + @mSQL3 + @mTmp2)
END
ExitProcess:
SET ANSI_WARNINGS ON
GO
--Ramesh
--Ramesh
-
Thanks for the repsonses. I will post schema and sample data shortly.
CSDunn
-
The tables and fields that form the query are as follows;
Table EOMVisaMC
[Name] nvarchar(30) NULL,
MID nvarchar(16) NULL,
RptYear int NULL,
RptMonth int NULL,
TotSalesVol money NULL,
TotRefVol money NULL,
Table MerchInfo
OpenDate datetime NULL
The query that the fields are used in looks like this;
SELECT TOP 100 PERCENT
EM.[Name],
EM.MID,
CONVERT(Varchar(9), MI.OpenDate,1) as OpenDate,
CONVERT(Varchar(4), EM.RptYear) + '-' + RIGHT('00' + CONVERT(Varchar(2), EM.RptMonth), 2) AS YearMonth,
EM.TotSalesVol AS SalesVolume,
EM.TotRefVol,
dbo.GetRatio(ISNULL(EM.TotRefVol, 0), ISNULL(EM.TotSalesVol, 0)) AS SalesRatio
FROM
dbo.VW_MerchInfo MI
INNER JOIN dbo.EOMVisaMC EM
ON MI.MID = EM.MID
WHERE
EM.TotSalesVol >0
AND
EM.TotRefVol > 0
GROUP BY
EM.[Name], EM.MID, MI.OpenDate, EM.RptMonth, EM.RptYear,
EM.TotRefVol, EM.TotSalesVol,
dbo.GetRatio(ISNULL(EM.TotRefVol, 0), ISNULL(EM.TotSalesVol,0))
ORDER BY
EM.MID, EM.RptYear, EM.RptMonth
A sample of the data;
Name
MID
OpenDate
YearMonth
SalesVolume
TotRefVol
SalesRatio
Joes Place
419304000010023
11/03/05
2005-11
18229.0400
20.00
1.09715
Joes Place
419304000010023
11/03/05
2006-04
40973.6100
1.00
2.44059
The names are merchant names and the MID is the merchantID. Individual merchants may have multiple locations, and thus have different MID’s and OpenDates. New records of SalesVolume, TotRefVol and SalesRatio are created for each ‘Month’ of the ‘Year’.
The way the data needs to be show would be as follows;
Name
MID
OpenDate
2005-11 SalesVolume
2005-11 TotRefVol
2005-11 SalesRatio
2005-04 SalesVolume
2005-04 TotRefVol
2005-04 SalesRatio
Joes Place
419304000010023
11/03/05
18229.0400
20.00
1.09715
40973.6100
1.00
2.44059
Each new record would add three new columns to the table.
Basically, that’s it. If you need additional information, please let me know.
Thanks again for your help!
CSDunn
-
I'll say... we need beer for these types of meetings. Good to "see" you again ...
--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) -
{EDIT} Never mind... I'm stupid
I don't need to do the calc...CS,This is important... please post the code for the GetRatio function ASAP... I'm almost done with your code and that's the missing link... thanks.--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) -
Shoot... I was getting ready to post the answer and saw that Ramesh had answered with code nearly identical to what I had made. Nice job, Ramesh
--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) -
Thanks jeff for the appreciation....
--Ramesh
-
Thanks to all again!
CSDunn
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply