Reshaping vertical data to horizontal layout.

Question

Post reply

Reshaping vertical data to horizontal layout.

HookSQLDBA7

SSChampion

Points: 10709
More actions
July 1, 2005 at 2:39 pm

#90131

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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)

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 2

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',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

Brenna Duncan-211418 Right there with Babe Points: 777 More actions · Answer 3

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

noeld SSC Guru Points: 96590 More actions · Answer 4

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

Charl Pienaar SSC Rookie Points: 37 More actions · Answer 5

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

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"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

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"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

What will happen when query is longer than 8,000 characters?

N 56°04'39.16"
E 12°55'05.25"