Reshaping vertical data to horizontal layout.
-
We have SQL Server 2000 SP3.
I have records as shown below
Country Ind Amount
ARG 1 10
ARG 1 10
ARG 1 10
ARG 2 20
ARG 3 10
AUS 4 10
AUS 4 20
AUS 5 30
Is it possible to get an ouput like a matrix
Ind
Country 1 2 3 4 5
ARG 30 20 10
AUS 30 30
I used the following code to reshape vertically oriented data into horizontally oriented Output. But, with the code below you must know the required number of Ind values.
SELECT
Country AS 'Country',
(CASE Ind WHEN 1 THEN Ind ELSE 0 END) AS Ind1,
(CASE Ind WHEN 2 THEN Ind ELSE 0 END) AS Ind2,
(CASE Ind WHEN 3 THEN Ind ELSE 0 END) AS Ind3,
(CASE Ind WHEN 4 THEN Ind ELSE 0 END) AS Ind4,
(CASE Ind WHEN 5 THEN Ind ELSE 0 END) AS Ind5,
Amount AS Amount
FROM TestMatrixOutput
Is there a better way of reshaping the data from vertical to horizontal?
Thanks in advance, Kevin
-
Yes, Thats how Crosstab queries work.
Why don't you want to use the IND in the query.
That is the only way to do it.
But you might want to modify code as so to actually get the totals
SELECT
Country AS 'Country',
Sum(CASE Ind WHEN 1 THEN Amount ELSE 0 END) AS Ind1,
Sum(CASE Ind WHEN 2 THEN Amount ELSE 0 END) AS Ind2,
Sum(CASE Ind WHEN 3 THEN Amount ELSE 0 END) AS Ind3,
Sum(CASE Ind WHEN 4 THEN Amount ELSE 0 END) AS Ind4,
Sum(CASE Ind WHEN 5 THEN Amount ELSE 0 END) AS Ind5,
FROM TestMatrixOutput
Group by Country
-- (Not tested)
-
Hi...
there's another soution, using dynamic sql... there is the code...
note that I'm ading an extra row to have an instance of a missing value for "ind", otherwise there would not be any column for this value....
CREATE TABLE #TestMatrixOutput (Country varchar(3), ind integer, amount integer)
INSERT INTO #TestMatrixOutput VALUES('ARG',1,10)
INSERT INTO #TestMatrixOutput VALUES('ARG',1,10)
INSERT INTO #TestMatrixOutput VALUES('ARG',7,10)
INSERT INTO #TestMatrixOutput VALUES('ARG',2,20)
INSERT INTO #TestMatrixOutput VALUES('ARG',3,10)
INSERT INTO #TestMatrixOutput VALUES('AUS',4,10)
INSERT INTO #TestMatrixOutput VALUES('AUS',4,20)
INSERT INTO #TestMatrixOutput VALUES('XXX',1,15)
INSERT INTO #TestMatrixOutput VALUES('XXX',3,15)
INSERT INTO #TestMatrixOutput VALUES('XXX',5,15)
-- extra line for taking care of missin "6" ind value...
INSERT INTO #TestMatrixOutput VALUES(null,6,null)
SELECT * FROM #TestMatrixOutput
-- the original code
SELECT Country AS 'Country',
Sum(CASE Ind WHEN 1 THEN Amount ELSE 0 END) AS Ind1,
Sum(CASE Ind WHEN 2 THEN Amount ELSE 0 END) AS Ind2,
Sum(CASE Ind WHEN 3 THEN Amount ELSE 0 END) AS Ind3,
Sum(CASE Ind WHEN 4 THEN Amount ELSE 0 END) AS Ind4,
Sum(CASE Ind WHEN 5 THEN Amount ELSE 0 END) AS Ind5
FROM #TestMatrixOutput
Group by Country
--the dynamic sql code
DECLARE @SQL varchar(4000)
SET @SQL = ''
SELECT @SQL = @SQL + ', Sum(CASE Ind WHEN '+CAST(ind as varchar(3))+' THEN Amount ELSE 0 END) AS Ind'+CAST(ind as varchar(3))
FROM #TestMatrixOutput
GROUP BY Ind
SET @SQL = 'SELECT Country AS Country ' + @SQL + ' FROM #TestMatrixOutput'
SET @SQL = @SQL + ' GROUP BY Country '
SET @SQL = @SQL + ' HAVING Country IS NOT NULL'
EXEC(@SQL)
DROP TABLE #TestMatrixOutput
Bye!!
Nicolas -
Here is another dynamic SQL option...
1. Create a temp table that has an identity column (RowNum) and an integer
column (IndValue).
CREATE TABLE #TempTable (
RowNum int Identity (1,1) Not Null,
IndValue int Not Null)
2. Populate the table with the available values of Ind (INSERT INTO #TempTable (IndValue) SELECT Ind FROM YourTable GROUP BY Ind. You'll get this...
RowNum IndValue
1 1
2 2
3 3
4 4
5 5
3. Now build a SQL string that will build a table and use the values
of IndValue from your temp table. You'll need a couple of variables to do
this.
DECLARE @MaxRow AS integer
DECLARE @CurRow AS integer
DECARE @Column AS integer (whatever the datatype of Ind is)
DECLARE @strsql AS varchar
SET @CurRow = 1
--start the SQL SELECT INTO statement
SET @strsql = 'SELECT COUNTRY, '
--get the number of rows in the temp table
SET @MaxRow = (SELECT Max(RowNum) FROM TempTable)
--loop through the temp table and append to the SQL SELECT INTO statement
WHILE @CurRow <=@MaxRow
BEGIN
--get the name of the column from the temp table
SET @column = (SELECT IndValue FROM TempTable WHERE RowNum = @CurRow)
--append it as a column name to the SQL SELECT INTO statement
SET @strsql = @strsql + @Column + '='
--get the value for the column
SET @strsql = @strsql + 'SUM(CASE WHEN Ind = ' + @Column + ' THEN Amount ELSE 0 END)'
'increment the row counter to get the next column name
SET @CurRow = @CurRow + 1
END
SET @strsql = @strsql + ') INTO YourResultTable'
EXEC(@strsql)
Your SQL SELECT INTO statement should be like this...
SELECT Country,
1 = SUM(CASE WHEN Ind = 1 THEN Amount ELSE 0 END),
2 = SUM(CASE WHEN Ind = 2 THEN Amount ELSE 0 END),
3 = SUM(CASE WHEN Ind = 3 THEN Amount ELSE 0 END), etc.
INTO YourResultTable GROUP BY Country
-
Do you really need to do the pivoting at server side ?
it will be simpler to just write :
SELECT Country, ind, SUM(Amount) as Amt
INTO YourTable
GROUP BY Country, ind
and perform pivoting client side
All dynamic solutions are usually faced with a lot of problems down the road
* Noel -
Hi
I am not totally getting this maybe you can help me please.
I have data thats return in a vertical manner like
Col 1 Col 2
Division Manager FourievdM
TranID 6232
Employee Number 0007307
Comments Annual Leave
Balance 7.74
Employee Name jamess
Date To 10 Nov 2005
Date From 8 Nov 2005
Employee Department SELBY
ResignationPeriod False
I need it to be col 1 as the field name and col2 the value of the field
Division Manager TranID Employee Number
FourievdM 6232 0007307
Is this possible any help please.
Charl
-
Beware of the 8,000 character limit for VARCHAR variables.
I wrote an article about pivoting a few weeks back. Please read it at http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp
N 56°04'39.16"
E 12°55'05.25" -
Do you have some field to distinguish between blocks of persons? Or this is only for one person always?
And please do not sub-threading other people's post. Start your own thread.
N 56°04'39.16"
E 12°55'05.25" -
What will happen when query is longer than 8,000 characters?
N 56°04'39.16"
E 12°55'05.25"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply