Pivot table

Question

Post reply

Pivot table

Andrew Collins

Ten Centuries

Points: 1369
More actions
August 9, 2005 at 6:06 am

#89338

Is there a way to produce a pivot table similar to the crosstab procedure on this site, which gives bandings of values accross the top rather than actual values. I have a database with employee sizes of companies which vary from 1 to 10000, it would be good to have the top row with 1 to 10, 11 to 20, 21 to 30 etc
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

With 1000 columns the query will be huge. May be this will give you an idea how to build the query.

SET NOCOUNT ON

/* GET DATA INTO TEMP TABLE START */

CREATE TABLE #Employee

(

CompanyID INT,

EmployeeSize INT

)

DECLARE @Ctr INT

SET @Ctr = 0

DECLARE @EmployeeSize INT

WHILE @Ctr < 100

BEGIN

SELECT @EmployeeSize = CONVERT(INT,SUBSTRING(REVERSE(CONVERT(VARCHAR,RAND(@Ctr))),1,4)) / 15

INSERT INTO #Employee VALUES (@Ctr, @EmployeeSize)

SET @Ctr = @Ctr + 1

END

/* GET DATA INTO TEMP TABLE END */

/* CREATE A DYNAMIC QUERY AND RETURN RESULTS */

DECLARE @SQL VARCHAR(8000)

DECLARE @EmpCtr INT

DECLARE @MaxCtr INT

SET @EmpCtr = 0

SELECT @MaxCtr = MAX(EmployeeSize) FROM #Employee

SELECT @SQL = 'SELECT '

WHILE @EmpCtr < @MaxCtr

BEGIN

SELECT @SQL = @SQL + '[' + CONVERT(VARCHAR,@EmpCtr + 1) + '-' + CONVERT(VARCHAR, @EmpCtr+10) + '] = SUM(CASE WHEN EmployeeSize BETWEEN ' + CONVERT(VARCHAR,@EmpCtr + 1) + ' AND ' + CONVERT(VARCHAR,@EmpCtr + 10) + ' THEN 1 ELSE 0 END)' + CASE WHEN @EmpCtr + 10 < @MaxCtr THEN ', ' ELSE ' ' END + CHAR(13)

SET @EmpCtr = @EmpCtr + 10

END

SELECT @SQL = @SQL + ' FROM #Employee'

--SELECT @SQL

EXEC(@SQL)

DROP TABLE #Employee

Regards,
gova

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Query will look like

SELECT

[1-10] = SUM(CASE WHEN EmployeeSize BETWEEN 1 AND 10 THEN 1 ELSE 0 END),

[11-20] = SUM(CASE WHEN EmployeeSize BETWEEN 11 AND 20 THEN 1 ELSE 0 END),

[21-30] = SUM(CASE WHEN EmployeeSize BETWEEN 21 AND 30 THEN 1 ELSE 0 END),

[31-40] = SUM(CASE WHEN EmployeeSize BETWEEN 31 AND 40 THEN 1 ELSE 0 END),

.......

[9991-10000] = SUM(CASE WHEN EmployeeSize BETWEEN 9991 AND 1000 THEN 1 ELSE 0 END)

FROM

Employee

Regards,
gova

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 3

thats great, just what i need. The employee ranges may change at times, but that is something i should be able to change.

Many Thanks

Andy.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

You are aware that this query would return a row with 1000 (one thousand) columns...?

..it's.. pretty wide...

/Kenneth

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 5

Yes, i will probably have to vary the bandings, depending on what sub set of the data i am producing the table on.

Thanks