Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 2nd, 2005, 05:06 AM
Authorized User
 
Join Date: Nov 2003
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Pivot Table

Hi all,
I needed a help in solving this problem .
While doing a select , query returns like this
date c1 c2 c3
12/2 12 4 8
12/5 14 5 9
12/6 15 6 10

but i need to get the out put like this ..

date 12/2 15/5 12/9

 c1 12 14 15
 c2 4 5 9
 c3 8 9 10

the probelm is date values are ever changing (not static)

Thanks in Advance,

Mukesh

 
Old February 9th, 2005, 10:10 AM
Authorized User
 
Join Date: Jul 2004
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found this somewhere, I forget exactly where so forgive me for not giving credit.

put this in your MASTER DB

execute it as an SP passing the parms as defined.

David

--LISTING 3: Header of the sp_CrossTab Stored Procedure

CREATE PROC sp_CrossTab
  @table AS sysname, -- Table to crosstab
  @onrows AS nvarchar(128), -- Grouping key values (on rows)
  @onrowsalias AS sysname = NULL, -- Alias for grouping column
  @oncols AS nvarchar(128), -- Destination columns (on columns)
  @sumcol AS sysname = NULL -- Data cells
AS


set @oncols = @ONCOLS


--LISTING 4: Step 1 of the sp_CrossTab Stored Procedure: Beginning of the SQL String

DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @sql =
  'SELECT' + @NEWLINE +
  ' ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS [' + @onrowsalias + ']'
    ELSE ''
  END

--LISTING 5: Step 2 of the sp_CrossTab Stored Procedure: Storing Keys in a Temp Table



CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table


--PRINT @keyssql + @NEWLINE -- For debug EXEC (@keyssql)


EXEC (@keyssql)

--LISTING 6: Step 3 of the sp_CrossTab Stored procedure: Middle Part of SQL String

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ',' + @NEWLINE +
    ' SUM(CASE CAST([' + @oncols +
                     '] AS nvarchar(100))' + @NEWLINE +
    ' WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '0' -- NULL SET TO 0 NOT 1
                          ELSE @sumcol
                        END + @NEWLINE +
    ' ELSE 0' + @NEWLINE +
    ' END) AS [' + @key +']'

  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

--LISTING 7: Step 4 of the sp_CrossTab Stored Procedure: End of SQL String

SET @sql = @sql + @NEWLINE +
  'FROM ' + @table + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

--PRINT @sql + @NEWLINE -- For debug

EXEC (@sql)
GO





Similar Threads
Thread Thread Starter Forum Replies Last Post
DefaultVersion in Pivot Table Amit Mohanty Excel VBA 1 August 1st, 2006 07:40 PM
Editing in Pivot Table brrmsc VBScript 0 November 29th, 2005 01:10 AM
Pivot Table vbsolo Excel VBA 3 November 23rd, 2005 01:28 AM
pivot table without total thelos Excel VBA 1 September 23rd, 2005 11:40 AM
Pivot Table ramdasu Excel VBA 0 October 23rd, 2003 03:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.