August 9, 2005 at 6:06 am
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
August 9, 2005 at 7:12 am
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
August 9, 2005 at 7:15 am
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
August 9, 2005 at 7:29 am
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.
August 9, 2005 at 7:42 am
You are aware that this query would return a row with 1000 (one thousand) columns...?
..it's.. pretty wide...
/Kenneth
August 9, 2005 at 7:47 am
Yes, i will probably have to vary the bandings, depending on what sub set of the data i am producing the table on.
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply