Making a CrossTab

Making a CrossTab

Post by Steen.Christianse » Wed, 16 May 2001 21:54:33



Hi There

I'm trying to make an output that looks like :

                       Company1     Company2    Company3...................
Item1                       0                    1                    1
Item2                        0                    0                    0
Item3                        1                    1                    1
.
.
.

The result shows that we are selling Item1 to Company2 an Company3. Item2 to
no one......

I have done the following SQL script, but I need two things :

The companyname in the headings, and to combine the results in one table.
The results I get looks like :

                --------
Item1            0
Item2            0
Item3            0
.
.
.

and

--------
    1
    0
    1

Thanks to any help.

Kind regards S*


DECLARE
Company_Cursur CURSOR FOR
SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
ORDER BY [Company no.]

OPEN Company_Cursur

FETCH NEXT FROM Company_Cursur


 SELECT [Item No.],[NAME], (select [Company Name]FROM V_Vare_Selskab_Matrix


 FROM V_Vare_Selskab_Matrix V
 GROUP BY [Item No.], [NAME]
 ORDER BY [Item No.]

 FETCH NEXT FROM Company_Cursur


 BEGIN


 SELECT

 FROM V_Vare_Selskab_Matrix V
 GROUP BY [Item No.]
 ORDER BY [Item No.]
 FETCH NEXT FROM Company_Cursur

 END
 CLOSE Company_Cursur

DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Steve Dassi » Thu, 17 May 2001 00:34:39


Check out RAC crosstab and pivoting utility
for server 7/2000 at:
www.angelfire.com/ny4/rac/
Inquire for new v1.50 beta for s2k.

 
 
 

Making a CrossTab

Post by Darren Brinksneade » Thu, 17 May 2001 00:52:27


Here is a simple crosstab procedure with some sample tables and executions
following the definition.  You can run the script as-is, it cleans up after
itself.

SET NOCOUNT ON
GO
CREATE PROC #pSimpleCrossTab




AS
/* This Procedure works with SQL2000 and SQL Server 7.0 */


CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
INSERT INTO #SCTTable




   BEGIN






NVARCHAR),'.','_'),CHAR(32),'_'),':','')
    ELSE --IF







     -- END IF

   END --WHILE




GO

CREATE TABLE #Project (
Project INT, Task INT, Start VARCHAR(8))

INSERT INTO #Project VALUES (12345,  160, '1/1/01')
INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
GO

#pSimpleCrossTab '#Project','Project','Task'
GO
#pSimpleCrossTab '#Project','Project','Task','start'
GO

CREATE TABLE #Comments
(unit_id INTEGER NOT NULL,
 comment_nbr INTEGER NOT NULL
          CHECK (comment_nbr > 0),
 comment VARCHAR(255) NOT NULL,
 PRIMARY KEY (unit_id, comment_nbr));

INSERT #Comments VALUES(1001,1,'Blah')
INSERT #Comments VALUES(1001,2,'BlahBlah')
INSERT #Comments VALUES(1002,1,'BlahBlah')
INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
INSERT #Comments VALUES(1003,2,'Blah')
INSERT #Comments VALUES(1003,3,'BlahBlah')
INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
GO

#pSimpleCrossTab '#Comments','unit_id','comment_nbr'
GO
#pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
GO

DROP TABLE #Comments
DROP TABLE #Project
DROP PROCEDURE #pSimpleCrossTab

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT


Quote:> Hi There

> I'm trying to make an output that looks like :

>                        Company1     Company2

Company3...................
> Item1                       0                    1                    1
> Item2                        0                    0                    0
> Item3                        1                    1                    1
> .
> .
> .

> The result shows that we are selling Item1 to Company2 an Company3. Item2
to
> no one......

> I have done the following SQL script, but I need two things :

> The companyname in the headings, and to combine the results in one table.
> The results I get looks like :

>                 --------
> Item1            0
> Item2            0
> Item3            0
> .
> .
> .

> and

> --------
>     1
>     0
>     1

> Thanks to any help.

> Kind regards S*

> DECLARE

> DECLARE
> Company_Cursur CURSOR FOR
> SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> ORDER BY [Company no.]

> OPEN Company_Cursur

> FETCH NEXT FROM Company_Cursur


>  SELECT [Item No.],[NAME], (select [Company Name]FROM

V_Vare_Selskab_Matrix

- Show quoted text -


>   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company no.] =

>  FROM V_Vare_Selskab_Matrix V
>  GROUP BY [Item No.], [NAME]
>  ORDER BY [Item No.]

>  FETCH NEXT FROM Company_Cursur


>  BEGIN


>  SELECT
>         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company no.]
=

>  FROM V_Vare_Selskab_Matrix V
>  GROUP BY [Item No.]
>  ORDER BY [Item No.]
>  FETCH NEXT FROM Company_Cursur

>  END
>  CLOSE Company_Cursur

> DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Steve Dassi » Thu, 17 May 2001 01:55:30


Hello Darren,

Congradulations on all your hard work.But be
advised that on this ng dynamic sql is frowned on.
Therefore only crosstabs whose columns are know
in advance are acceptable.

stevie

 
 
 

Making a CrossTab

Post by Darren Brinksneade » Thu, 17 May 2001 02:09:26


The whole purose of a dynamic crosstab is that you don't know the number of
columns that will be returned.

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT


Quote:

> Hello Darren,

> Congradulations on all your hard work.But be
> advised that on this ng dynamic sql is frowned on.
> Therefore only crosstabs whose columns are know
> in advance are acceptable.

> stevie

 
 
 

Making a CrossTab

Post by Steen.Christianse » Thu, 17 May 2001 17:39:20


Hi Darren

Thats just what I was looking for. Thanks a lot.


the max size is 8000. Do you have any good ideas ?

Kind regards S*



> Here is a simple crosstab procedure with some sample tables and executions
> following the definition.  You can run the script as-is, it cleans up
after
> itself.

> SET NOCOUNT ON
> GO
> CREATE PROC #pSimpleCrossTab




> AS
> /* This Procedure works with SQL2000 and SQL Server 7.0 */


> CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
> INSERT INTO #SCTTable




>    BEGIN





> VARCHAR(255)) + ''' ) IS NOT NULL THEN ''YES'' ELSE ''NO'' END AS ' +

> NVARCHAR),'.','_'),CHAR(32),'_'),':','')
>     ELSE --IF





'


>      -- END IF

>    END --WHILE




> GO

> CREATE TABLE #Project (
> Project INT, Task INT, Start VARCHAR(8))

> INSERT INTO #Project VALUES (12345,  160, '1/1/01')
> INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
> INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
> INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
> INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
> GO

> #pSimpleCrossTab '#Project','Project','Task'
> GO
> #pSimpleCrossTab '#Project','Project','Task','start'
> GO

> CREATE TABLE #Comments
> (unit_id INTEGER NOT NULL,
>  comment_nbr INTEGER NOT NULL
>           CHECK (comment_nbr > 0),
>  comment VARCHAR(255) NOT NULL,
>  PRIMARY KEY (unit_id, comment_nbr));

> INSERT #Comments VALUES(1001,1,'Blah')
> INSERT #Comments VALUES(1001,2,'BlahBlah')
> INSERT #Comments VALUES(1002,1,'BlahBlah')
> INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
> INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
> INSERT #Comments VALUES(1003,2,'Blah')
> INSERT #Comments VALUES(1003,3,'BlahBlah')
> INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
> INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
> GO

> #pSimpleCrossTab '#Comments','unit_id','comment_nbr'
> GO
> #pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
> GO

> DROP TABLE #Comments
> DROP TABLE #Project
> DROP PROCEDURE #pSimpleCrossTab

> --
> Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT



> > Hi There

> > I'm trying to make an output that looks like :

> >                        Company1     Company2
> Company3...................
> > Item1                       0                    1                    1
> > Item2                        0                    0                    0
> > Item3                        1                    1                    1
> > .
> > .
> > .

> > The result shows that we are selling Item1 to Company2 an Company3.
Item2
> to
> > no one......

> > I have done the following SQL script, but I need two things :

> > The companyname in the headings, and to combine the results in one
table.
> > The results I get looks like :

> >                 --------
> > Item1            0
> > Item2            0
> > Item3            0
> > .
> > .
> > .

> > and

> > --------
> >     1
> >     0
> >     1

> > Thanks to any help.

> > Kind regards S*

> > DECLARE

> > DECLARE
> > Company_Cursur CURSOR FOR
> > SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> > ORDER BY [Company no.]

> > OPEN Company_Cursur

> > FETCH NEXT FROM Company_Cursur


> >  SELECT [Item No.],[NAME], (select [Company Name]FROM
> V_Vare_Selskab_Matrix

Company,
> >   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company no.] =

> >  FROM V_Vare_Selskab_Matrix V
> >  GROUP BY [Item No.], [NAME]
> >  ORDER BY [Item No.]

> >  FETCH NEXT FROM Company_Cursur


> >  BEGIN


> >  SELECT
> >         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company
no.]
> =

> >  FROM V_Vare_Selskab_Matrix V
> >  GROUP BY [Item No.]
> >  ORDER BY [Item No.]
> >  FETCH NEXT FROM Company_Cursur

> >  END
> >  CLOSE Company_Cursur

> > DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Tibor Karasz » Thu, 17 May 2001 19:51:36


S*,

In general, you can use several variables, like:


You'd have to consider whether that is doable for Darren's script...

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.veryComputer.com/


> Hi Darren

> Thats just what I was looking for. Thanks a lot.


> the max size is 8000. Do you have any good ideas ?

> Kind regards S*



> > Here is a simple crosstab procedure with some sample tables and executions
> > following the definition.  You can run the script as-is, it cleans up
> after
> > itself.

> > SET NOCOUNT ON
> > GO
> > CREATE PROC #pSimpleCrossTab




> > AS
> > /* This Procedure works with SQL2000 and SQL Server 7.0 */


> > CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
> > INSERT INTO #SCTTable




> >    BEGIN





> > VARCHAR(255)) + ''' ) IS NOT NULL THEN ''YES'' ELSE ''NO'' END AS ' +

> > NVARCHAR),'.','_'),CHAR(32),'_'),':','')
> >     ELSE --IF





> '


> >      -- END IF

> >    END --WHILE




> > GO

> > CREATE TABLE #Project (
> > Project INT, Task INT, Start VARCHAR(8))

> > INSERT INTO #Project VALUES (12345,  160, '1/1/01')
> > INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
> > INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
> > INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
> > INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
> > GO

> > #pSimpleCrossTab '#Project','Project','Task'
> > GO
> > #pSimpleCrossTab '#Project','Project','Task','start'
> > GO

> > CREATE TABLE #Comments
> > (unit_id INTEGER NOT NULL,
> >  comment_nbr INTEGER NOT NULL
> >           CHECK (comment_nbr > 0),
> >  comment VARCHAR(255) NOT NULL,
> >  PRIMARY KEY (unit_id, comment_nbr));

> > INSERT #Comments VALUES(1001,1,'Blah')
> > INSERT #Comments VALUES(1001,2,'BlahBlah')
> > INSERT #Comments VALUES(1002,1,'BlahBlah')
> > INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
> > INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
> > INSERT #Comments VALUES(1003,2,'Blah')
> > INSERT #Comments VALUES(1003,3,'BlahBlah')
> > INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
> > INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
> > GO

> > #pSimpleCrossTab '#Comments','unit_id','comment_nbr'
> > GO
> > #pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
> > GO

> > DROP TABLE #Comments
> > DROP TABLE #Project
> > DROP PROCEDURE #pSimpleCrossTab

> > --
> > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT



> > > Hi There

> > > I'm trying to make an output that looks like :

> > >                        Company1     Company2
> > Company3...................
> > > Item1                       0                    1                    1
> > > Item2                        0                    0                    0
> > > Item3                        1                    1                    1
> > > .
> > > .
> > > .

> > > The result shows that we are selling Item1 to Company2 an Company3.
> Item2
> > to
> > > no one......

> > > I have done the following SQL script, but I need two things :

> > > The companyname in the headings, and to combine the results in one
> table.
> > > The results I get looks like :

> > >                 --------
> > > Item1            0
> > > Item2            0
> > > Item3            0
> > > .
> > > .
> > > .

> > > and

> > > --------
> > >     1
> > >     0
> > >     1

> > > Thanks to any help.

> > > Kind regards S*

> > > DECLARE

> > > DECLARE
> > > Company_Cursur CURSOR FOR
> > > SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> > > ORDER BY [Company no.]

> > > OPEN Company_Cursur

> > > FETCH NEXT FROM Company_Cursur


> > >  SELECT [Item No.],[NAME], (select [Company Name]FROM
> > V_Vare_Selskab_Matrix

> Company,
> > >   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company no.] =

> > >  FROM V_Vare_Selskab_Matrix V
> > >  GROUP BY [Item No.], [NAME]
> > >  ORDER BY [Item No.]

> > >  FETCH NEXT FROM Company_Cursur


> > >  BEGIN


> > >  SELECT
> > >         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company
> no.]
> > =

> > >  FROM V_Vare_Selskab_Matrix V
> > >  GROUP BY [Item No.]
> > >  ORDER BY [Item No.]
> > >  FETCH NEXT FROM Company_Cursur

> > >  END
> > >  CLOSE Company_Cursur

> > > DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Darren Brinksneade » Thu, 17 May 2001 22:33:25


You can do it using Tibor's example.  Check the length of the SQLStr 'SELECT
LEN(@SQLStr)' be for you enter the loop.  If it is greater that 7500 then
SET @<mynewstringname> = @SQLStr  and then SET @SQLStr  = ''.  This should
give you 1500 possible in the SQL statement length.  If you need more than
that you can repeat process by adding a third string variable and so on...

Then EXEC(@str1 + @str2 + ...)

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

"Tibor Karaszi" <tibor.please_reply_to_public_forum.kara...@cornerstone.se>
wrote in message news:#FIBtYf3AHA.1264@tkmsftngp05...

> Steen,

> In general, you can use several variables, like:

> EXEC(@str1 + @str2).

> You'd have to consider whether that is doable for Darren's script...

> --
> Tibor Karaszi, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com

> "Steen.Christiansen" <s...@3l.dk> wrote in message

news:eh3PxPe3AHA.2076@tkmsftngp07...
> > Hi Darren

> > Thats just what I was looking for. Thanks a lot.

> > I ran in to the problem that the variable @SQLStr is too small. I know
that
> > the max size is 8000. Do you have any good ideas ?

> > Kind regards Steen

> > "Darren Brinksneader" <dbrinksnea...@atai.com> skrev i en meddelelse
> > news:eSisDeV3AHA.1440@tkmsftngp07...
> > > Here is a simple crosstab procedure with some sample tables and
executions
> > > following the definition.  You can run the script as-is, it cleans up
> > after
> > > itself.

> > > SET NOCOUNT ON
> > > GO
> > > CREATE PROC #pSimpleCrossTab
> > >   @TableName AS sysname,
> > >   @DistinctRow AS nvarchar(128),
> > >   @PivotColumn AS nvarchar(128),
> > >   @PivotData AS nvarchar(128) = NULL
> > > AS
> > > /* This Procedure works with SQL2000 and SQL Server 7.0 */

> > > DECLARE  @SQLStr varchar(8000), @COL VARCHAR(255)
> > > CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
> > > INSERT INTO #SCTTable
> > >  EXEC ('SELECT DISTINCT ' + @PivotColumn + ' FROM ' + @TableName)

> > > SELECT @SQLStr = 'SELECT DISTINCT ' + @DistinctRow
> > > SELECT @COL = MIN(PIV_COLS) FROM #SCTTable

> > >   WHILE @COL <= (SELECT MAX(PIV_COLS) FROM #SCTTable)
> > >    BEGIN
> > >     IF @PivotData IS NULL
> > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' + @PivotColumn +
'
> > >  FROM ' +  @TableName  + ' t2
> > > WHERE t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > >   AND CAST(' + @PivotColumn + ' as VARCHAR) = ''' + cast(@COL AS
> > > VARCHAR(255)) + ''' ) IS NOT NULL THEN ''YES'' ELSE ''NO'' END AS ' +
> > > @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > NVARCHAR),'.','_'),CHAR(32),'_'),':','')
> > >     ELSE --IF
> > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +  @PivotData +
> > > ' FROM ' +  @TableName  + ' t2
> > > WHERE  t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > >   AND  CAST(' + @PivotColumn + ' as VARCHAR) =  ''' + cast(@COL AS
> > > VARCHAR(255)) + ''' ) IS NULL THEN CHAR(32) ELSE ' + @PivotData + '
END AS
> > '
> > > + @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > NVARCHAR),'.','_'),CHAR(32),'_'),':','') + '_' + @PivotData
> > >      -- END IF
> > >       SELECT @COL = MIN(PIV_COLS) FROM #SCTTable WHERE PIV_COLS > @COL
> > >    END --WHILE

> > > SET @SQLStr = @SQLStr +  ' FROM ' + @tablename + ' t1'

> > > --PRINT @SQLStr --for DEBUGGING
> > > EXEC (@SQLStr)
> > > GO

> > > CREATE TABLE #Project (
> > > Project INT, Task INT, Start VARCHAR(8))

> > > INSERT INTO #Project VALUES (12345,  160, '1/1/01')
> > > INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
> > > INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
> > > INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
> > > INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
> > > GO

> > > #pSimpleCrossTab '#Project','Project','Task'
> > > GO
> > > #pSimpleCrossTab '#Project','Project','Task','start'
> > > GO

> > > CREATE TABLE #Comments
> > > (unit_id INTEGER NOT NULL,
> > >  comment_nbr INTEGER NOT NULL
> > >           CHECK (comment_nbr > 0),
> > >  comment VARCHAR(255) NOT NULL,
> > >  PRIMARY KEY (unit_id, comment_nbr));

> > > INSERT #Comments VALUES(1001,1,'Blah')
> > > INSERT #Comments VALUES(1001,2,'BlahBlah')
> > > INSERT #Comments VALUES(1002,1,'BlahBlah')
> > > INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
> > > INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
> > > INSERT #Comments VALUES(1003,2,'Blah')
> > > INSERT #Comments VALUES(1003,3,'BlahBlah')
> > > INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
> > > INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
> > > GO

> > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr'
> > > GO
> > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
> > > GO

> > > DROP TABLE #Comments
> > > DROP TABLE #Project
> > > DROP PROCEDURE #pSimpleCrossTab

> > > --
> > > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> > > "Steen.Christiansen" <s...@3l.dk> wrote in message
> > > news:OmXKj5T3AHA.1692@tkmsftngp02...
> > > > Hi There

> > > > I'm trying to make an output that looks like :

> > > >                        Company1     Company2
> > > Company3...................
> > > > Item1                       0                    1
1
> > > > Item2                        0                    0
0
> > > > Item3                        1                    1
1
> > > > .
> > > > .
> > > > .

> > > > The result shows that we are selling Item1 to Company2 an Company3.
> > Item2
> > > to
> > > > no one......

> > > > I have done the following SQL script, but I need two things :

> > > > The companyname in the headings, and to combine the results in one
> > table.
> > > > The results I get looks like :

> > > >                 --------
> > > > Item1            0
> > > > Item2            0
> > > > Item3            0
> > > > .
> > > > .
> > > > .

> > > > and

> > > > --------
> > > >     1
> > > >     0
> > > >     1

> > > > Thanks to any help.

> > > > Kind regards Steen
> > > > s...@3L.DK

> > > > DECLARE
> > > > @company int

> > > > DECLARE
> > > > Company_Cursur CURSOR FOR
> > > > SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> > > > ORDER BY [Company no.]

> > > > OPEN Company_Cursur

> > > > FETCH NEXT FROM Company_Cursur
> > > > INTO @company

> > > >  PRINT @company

> > > >  SELECT [Item No.],[NAME], (select [Company Name]FROM
> > > V_Vare_Selskab_Matrix
> > > > WHERE [Company no.] = @company AND [Item No.] = V.[Item No.]) AS
> > Company,
> > > >   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company no.]
=
> > > > @company AND [Item No.] = V.[Item No.]),0)

> > > >  FROM V_Vare_Selskab_Matrix V
> > > >  GROUP BY [Item No.], [NAME]
> > > >  ORDER BY [Item No.]

> > > >  FETCH NEXT FROM Company_Cursur
> > > >  INTO @company

> > > >  WHILE @@FETCH_STATUS = 0
> > > >  BEGIN

> > > >  PRINT @company

> > > >  SELECT
> > > >         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE
[Company
> > no.]
> > > =
> > > > @company AND [Item No.] = V.[Item No.]),0)
> > > >  FROM V_Vare_Selskab_Matrix V
> > > >  GROUP BY [Item No.]
> > > >  ORDER BY [Item No.]
> > > >  FETCH NEXT FROM Company_Cursur
> > > >  INTO @company

> > > >  END
> > > >  CLOSE Company_Cursur

> > > > DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Darren Brinksneade » Thu, 17 May 2001 22:39:17


Oops! I meant to type 15000.  There is also a limit to the number of columns
that can be in the select: 4096.  Hopefully you won't exceed that! :-)

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

"Darren Brinksneader" <dbrinksnea...@atai.com> wrote in message

news:OK$cD1g3AHA.948@tkmsftngp02...
> You can do it using Tibor's example.  Check the length of the SQLStr
'SELECT
> LEN(@SQLStr)' be for you enter the loop.  If it is greater that 7500 then
> SET @<mynewstringname> = @SQLStr  and then SET @SQLStr  = ''.  This should
> give you 1500 possible in the SQL statement length.  If you need more than
> that you can repeat process by adding a third string variable and so on...

> Then EXEC(@str1 + @str2 + ...)

> --
> Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> "Tibor Karaszi"

<tibor.please_reply_to_public_forum.kara...@cornerstone.se>
> wrote in message news:#FIBtYf3AHA.1264@tkmsftngp05...
> > Steen,

> > In general, you can use several variables, like:

> > EXEC(@str1 + @str2).

> > You'd have to consider whether that is doable for Darren's script...

> > --
> > Tibor Karaszi, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com

> > "Steen.Christiansen" <s...@3l.dk> wrote in message
> news:eh3PxPe3AHA.2076@tkmsftngp07...
> > > Hi Darren

> > > Thats just what I was looking for. Thanks a lot.

> > > I ran in to the problem that the variable @SQLStr is too small. I know
> that
> > > the max size is 8000. Do you have any good ideas ?

> > > Kind regards Steen

> > > "Darren Brinksneader" <dbrinksnea...@atai.com> skrev i en meddelelse
> > > news:eSisDeV3AHA.1440@tkmsftngp07...
> > > > Here is a simple crosstab procedure with some sample tables and
> executions
> > > > following the definition.  You can run the script as-is, it cleans
up
> > > after
> > > > itself.

> > > > SET NOCOUNT ON
> > > > GO
> > > > CREATE PROC #pSimpleCrossTab
> > > >   @TableName AS sysname,
> > > >   @DistinctRow AS nvarchar(128),
> > > >   @PivotColumn AS nvarchar(128),
> > > >   @PivotData AS nvarchar(128) = NULL
> > > > AS
> > > > /* This Procedure works with SQL2000 and SQL Server 7.0 */

> > > > DECLARE  @SQLStr varchar(8000), @COL VARCHAR(255)
> > > > CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
> > > > INSERT INTO #SCTTable
> > > >  EXEC ('SELECT DISTINCT ' + @PivotColumn + ' FROM ' + @TableName)

> > > > SELECT @SQLStr = 'SELECT DISTINCT ' + @DistinctRow
> > > > SELECT @COL = MIN(PIV_COLS) FROM #SCTTable

> > > >   WHILE @COL <= (SELECT MAX(PIV_COLS) FROM #SCTTable)
> > > >    BEGIN
> > > >     IF @PivotData IS NULL
> > > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' + @PivotColumn
+
> '
> > > >  FROM ' +  @TableName  + ' t2
> > > > WHERE t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > > >   AND CAST(' + @PivotColumn + ' as VARCHAR) = ''' + cast(@COL AS
> > > > VARCHAR(255)) + ''' ) IS NOT NULL THEN ''YES'' ELSE ''NO'' END AS '
+
> > > > @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > > NVARCHAR),'.','_'),CHAR(32),'_'),':','')
> > > >     ELSE --IF
> > > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +  @PivotData +
> > > > ' FROM ' +  @TableName  + ' t2
> > > > WHERE  t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > > >   AND  CAST(' + @PivotColumn + ' as VARCHAR) =  ''' + cast(@COL AS
> > > > VARCHAR(255)) + ''' ) IS NULL THEN CHAR(32) ELSE ' + @PivotData + '
> END AS
> > > '
> > > > + @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > > NVARCHAR),'.','_'),CHAR(32),'_'),':','') + '_' + @PivotData
> > > >      -- END IF
> > > >       SELECT @COL = MIN(PIV_COLS) FROM #SCTTable WHERE PIV_COLS >
@COL
> > > >    END --WHILE

> > > > SET @SQLStr = @SQLStr +  ' FROM ' + @tablename + ' t1'

> > > > --PRINT @SQLStr --for DEBUGGING
> > > > EXEC (@SQLStr)
> > > > GO

> > > > CREATE TABLE #Project (
> > > > Project INT, Task INT, Start VARCHAR(8))

> > > > INSERT INTO #Project VALUES (12345,  160, '1/1/01')
> > > > INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
> > > > INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
> > > > INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
> > > > INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
> > > > GO

> > > > #pSimpleCrossTab '#Project','Project','Task'
> > > > GO
> > > > #pSimpleCrossTab '#Project','Project','Task','start'
> > > > GO

> > > > CREATE TABLE #Comments
> > > > (unit_id INTEGER NOT NULL,
> > > >  comment_nbr INTEGER NOT NULL
> > > >           CHECK (comment_nbr > 0),
> > > >  comment VARCHAR(255) NOT NULL,
> > > >  PRIMARY KEY (unit_id, comment_nbr));

> > > > INSERT #Comments VALUES(1001,1,'Blah')
> > > > INSERT #Comments VALUES(1001,2,'BlahBlah')
> > > > INSERT #Comments VALUES(1002,1,'BlahBlah')
> > > > INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
> > > > INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
> > > > INSERT #Comments VALUES(1003,2,'Blah')
> > > > INSERT #Comments VALUES(1003,3,'BlahBlah')
> > > > INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
> > > > INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
> > > > GO

> > > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr'
> > > > GO
> > > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
> > > > GO

> > > > DROP TABLE #Comments
> > > > DROP TABLE #Project
> > > > DROP PROCEDURE #pSimpleCrossTab

> > > > --
> > > > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> > > > "Steen.Christiansen" <s...@3l.dk> wrote in message
> > > > news:OmXKj5T3AHA.1692@tkmsftngp02...
> > > > > Hi There

> > > > > I'm trying to make an output that looks like :

> > > > >                        Company1     Company2
> > > > Company3...................
> > > > > Item1                       0                    1
> 1
> > > > > Item2                        0                    0
> 0
> > > > > Item3                        1                    1
> 1
> > > > > .
> > > > > .
> > > > > .

> > > > > The result shows that we are selling Item1 to Company2 an
Company3.
> > > Item2
> > > > to
> > > > > no one......

> > > > > I have done the following SQL script, but I need two things :

> > > > > The companyname in the headings, and to combine the results in one
> > > table.
> > > > > The results I get looks like :

> > > > >                 --------
> > > > > Item1            0
> > > > > Item2            0
> > > > > Item3            0
> > > > > .
> > > > > .
> > > > > .

> > > > > and

> > > > > --------
> > > > >     1
> > > > >     0
> > > > >     1

> > > > > Thanks to any help.

> > > > > Kind regards Steen
> > > > > s...@3L.DK

> > > > > DECLARE
> > > > > @company int

> > > > > DECLARE
> > > > > Company_Cursur CURSOR FOR
> > > > > SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> > > > > ORDER BY [Company no.]

> > > > > OPEN Company_Cursur

> > > > > FETCH NEXT FROM Company_Cursur
> > > > > INTO @company

> > > > >  PRINT @company

> > > > >  SELECT [Item No.],[NAME], (select [Company Name]FROM
> > > > V_Vare_Selskab_Matrix
> > > > > WHERE [Company no.] = @company AND [Item No.] = V.[Item No.]) AS
> > > Company,
> > > > >   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company
no.]
> =
> > > > > @company AND [Item No.] = V.[Item No.]),0)

> > > > >  FROM V_Vare_Selskab_Matrix V
> > > > >  GROUP BY [Item No.], [NAME]
> > > > >  ORDER BY [Item No.]

> > > > >  FETCH NEXT FROM Company_Cursur
> > > > >  INTO @company

> > > > >  WHILE @@FETCH_STATUS = 0
> > > > >  BEGIN

> > > > >  PRINT @company

> > > > >  SELECT
> > > > >         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE
> [Company
> > > no.]
> > > > =
> > > > > @company AND [Item No.] = V.[Item No.]),0)
> > > > >  FROM V_Vare_Selskab_Matrix V
> > > > >  GROUP BY [Item No.]
> > > > >  ORDER BY [Item No.]
> > > > >  FETCH NEXT FROM Company_Cursur
> > > > >  INTO @company

> > > > >  END
> > > > >  CLOSE Company_Cursur

> > > > > DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Steen.Christianse » Fri, 18 May 2001 15:20:41


Thanks a lot.

"Darren Brinksneader" <dbrinksnea...@atai.com> skrev i en meddelelse
news:#iNbV4g3AHA.1992@tkmsftngp05...

> Oops! I meant to type 15000.  There is also a limit to the number of
columns
> that can be in the select: 4096.  Hopefully you won't exceed that! :-)

> --
> Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> "Darren Brinksneader" <dbrinksnea...@atai.com> wrote in message
> news:OK$cD1g3AHA.948@tkmsftngp02...
> > You can do it using Tibor's example.  Check the length of the SQLStr
> 'SELECT
> > LEN(@SQLStr)' be for you enter the loop.  If it is greater that 7500
then
> > SET @<mynewstringname> = @SQLStr  and then SET @SQLStr  = ''.  This
should
> > give you 1500 possible in the SQL statement length.  If you need more
than
> > that you can repeat process by adding a third string variable and so
on...

> > Then EXEC(@str1 + @str2 + ...)

> > --
> > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.kara...@cornerstone.se>
> > wrote in message news:#FIBtYf3AHA.1264@tkmsftngp05...
> > > Steen,

> > > In general, you can use several variables, like:

> > > EXEC(@str1 + @str2).

> > > You'd have to consider whether that is doable for Darren's script...

> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > FAQ from Neil & others at: http://www.sqlserverfaq.com

> > > "Steen.Christiansen" <s...@3l.dk> wrote in message
> > news:eh3PxPe3AHA.2076@tkmsftngp07...
> > > > Hi Darren

> > > > Thats just what I was looking for. Thanks a lot.

> > > > I ran in to the problem that the variable @SQLStr is too small. I
know
> > that
> > > > the max size is 8000. Do you have any good ideas ?

> > > > Kind regards Steen

> > > > "Darren Brinksneader" <dbrinksnea...@atai.com> skrev i en meddelelse
> > > > news:eSisDeV3AHA.1440@tkmsftngp07...
> > > > > Here is a simple crosstab procedure with some sample tables and
> > executions
> > > > > following the definition.  You can run the script as-is, it cleans
> up
> > > > after
> > > > > itself.

> > > > > SET NOCOUNT ON
> > > > > GO
> > > > > CREATE PROC #pSimpleCrossTab
> > > > >   @TableName AS sysname,
> > > > >   @DistinctRow AS nvarchar(128),
> > > > >   @PivotColumn AS nvarchar(128),
> > > > >   @PivotData AS nvarchar(128) = NULL
> > > > > AS
> > > > > /* This Procedure works with SQL2000 and SQL Server 7.0 */

> > > > > DECLARE  @SQLStr varchar(8000), @COL VARCHAR(255)
> > > > > CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
> > > > > INSERT INTO #SCTTable
> > > > >  EXEC ('SELECT DISTINCT ' + @PivotColumn + ' FROM ' + @TableName)

> > > > > SELECT @SQLStr = 'SELECT DISTINCT ' + @DistinctRow
> > > > > SELECT @COL = MIN(PIV_COLS) FROM #SCTTable

> > > > >   WHILE @COL <= (SELECT MAX(PIV_COLS) FROM #SCTTable)
> > > > >    BEGIN
> > > > >     IF @PivotData IS NULL
> > > > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +
@PivotColumn
> +
> > '
> > > > >  FROM ' +  @TableName  + ' t2
> > > > > WHERE t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > > > >   AND CAST(' + @PivotColumn + ' as VARCHAR) = ''' + cast(@COL AS
> > > > > VARCHAR(255)) + ''' ) IS NOT NULL THEN ''YES'' ELSE ''NO'' END AS
'
> +
> > > > > @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > > > NVARCHAR),'.','_'),CHAR(32),'_'),':','')
> > > > >     ELSE --IF
> > > > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +  @PivotData
+
> > > > > ' FROM ' +  @TableName  + ' t2
> > > > > WHERE  t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > > > >   AND  CAST(' + @PivotColumn + ' as VARCHAR) =  ''' + cast(@COL AS
> > > > > VARCHAR(255)) + ''' ) IS NULL THEN CHAR(32) ELSE ' + @PivotData +
'
> > END AS
> > > > '
> > > > > + @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > > > NVARCHAR),'.','_'),CHAR(32),'_'),':','') + '_' + @PivotData
> > > > >      -- END IF
> > > > >       SELECT @COL = MIN(PIV_COLS) FROM #SCTTable WHERE PIV_COLS >
> @COL
> > > > >    END --WHILE

> > > > > SET @SQLStr = @SQLStr +  ' FROM ' + @tablename + ' t1'

> > > > > --PRINT @SQLStr --for DEBUGGING
> > > > > EXEC (@SQLStr)
> > > > > GO

> > > > > CREATE TABLE #Project (
> > > > > Project INT, Task INT, Start VARCHAR(8))

> > > > > INSERT INTO #Project VALUES (12345,  160, '1/1/01')
> > > > > INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
> > > > > INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
> > > > > INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
> > > > > INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
> > > > > GO

> > > > > #pSimpleCrossTab '#Project','Project','Task'
> > > > > GO
> > > > > #pSimpleCrossTab '#Project','Project','Task','start'
> > > > > GO

> > > > > CREATE TABLE #Comments
> > > > > (unit_id INTEGER NOT NULL,
> > > > >  comment_nbr INTEGER NOT NULL
> > > > >           CHECK (comment_nbr > 0),
> > > > >  comment VARCHAR(255) NOT NULL,
> > > > >  PRIMARY KEY (unit_id, comment_nbr));

> > > > > INSERT #Comments VALUES(1001,1,'Blah')
> > > > > INSERT #Comments VALUES(1001,2,'BlahBlah')
> > > > > INSERT #Comments VALUES(1002,1,'BlahBlah')
> > > > > INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
> > > > > INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
> > > > > INSERT #Comments VALUES(1003,2,'Blah')
> > > > > INSERT #Comments VALUES(1003,3,'BlahBlah')
> > > > > INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
> > > > > INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
> > > > > GO

> > > > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr'
> > > > > GO
> > > > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
> > > > > GO

> > > > > DROP TABLE #Comments
> > > > > DROP TABLE #Project
> > > > > DROP PROCEDURE #pSimpleCrossTab

> > > > > --
> > > > > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> > > > > "Steen.Christiansen" <s...@3l.dk> wrote in message
> > > > > news:OmXKj5T3AHA.1692@tkmsftngp02...
> > > > > > Hi There

> > > > > > I'm trying to make an output that looks like :

> > > > > >                        Company1     Company2
> > > > > Company3...................
> > > > > > Item1                       0                    1
> > 1
> > > > > > Item2                        0                    0
> > 0
> > > > > > Item3                        1                    1
> > 1
> > > > > > .
> > > > > > .
> > > > > > .

> > > > > > The result shows that we are selling Item1 to Company2 an
> Company3.
> > > > Item2
> > > > > to
> > > > > > no one......

> > > > > > I have done the following SQL script, but I need two things :

> > > > > > The companyname in the headings, and to combine the results in
one
> > > > table.
> > > > > > The results I get looks like :

> > > > > >                 --------
> > > > > > Item1            0
> > > > > > Item2            0
> > > > > > Item3            0
> > > > > > .
> > > > > > .
> > > > > > .

> > > > > > and

> > > > > > --------
> > > > > >     1
> > > > > >     0
> > > > > >     1

> > > > > > Thanks to any help.

> > > > > > Kind regards Steen
> > > > > > s...@3L.DK

> > > > > > DECLARE
> > > > > > @company int

> > > > > > DECLARE
> > > > > > Company_Cursur CURSOR FOR
> > > > > > SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> > > > > > ORDER BY [Company no.]

> > > > > > OPEN Company_Cursur

> > > > > > FETCH NEXT FROM Company_Cursur
> > > > > > INTO @company

> > > > > >  PRINT @company

> > > > > >  SELECT [Item No.],[NAME], (select [Company Name]FROM
> > > > > V_Vare_Selskab_Matrix
> > > > > > WHERE [Company no.] = @company AND [Item No.] = V.[Item No.]) AS
> > > > Company,
> > > > > >   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company
> no.]
> > =
> > > > > > @company AND [Item No.] = V.[Item No.]),0)

> > > > > >  FROM V_Vare_Selskab_Matrix V
> > > > > >  GROUP BY [Item No.], [NAME]
> > > > > >  ORDER BY [Item No.]

> > > > > >  FETCH NEXT FROM Company_Cursur
> > > > > >  INTO @company

> > > > > >  WHILE @@FETCH_STATUS = 0
> > > > > >  BEGIN

> > > > > >  PRINT @company

> > > > > >  SELECT
> > > > > >         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE
> > [Company
> > > > no.]
> > > > > =
> > > > > > @company AND [Item No.] = V.[Item No.]),0)
> > > > > >  FROM V_Vare_Selskab_Matrix V
> > > > > >  GROUP BY [Item No.]
> > > > > >  ORDER BY [Item No.]
> > > > > >  FETCH NEXT FROM Company_Cursur
> > > > > >  INTO @company

> > > > > >  END
> > > > > >  CLOSE Company_Cursur

> > > > > > DEALLOCATE Company_Cursur

 
 
 

Making a CrossTab

Post by Steen.Christianse » Fri, 18 May 2001 15:21:48


Thanks a lot.

/Steen

"Darren Brinksneader" <dbrinksnea...@atai.com> skrev i en meddelelse
news:#iNbV4g3AHA.1992@tkmsftngp05...

> Oops! I meant to type 15000.  There is also a limit to the number of
columns
> that can be in the select: 4096.  Hopefully you won't exceed that! :-)

> --
> Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> "Darren Brinksneader" <dbrinksnea...@atai.com> wrote in message
> news:OK$cD1g3AHA.948@tkmsftngp02...
> > You can do it using Tibor's example.  Check the length of the SQLStr
> 'SELECT
> > LEN(@SQLStr)' be for you enter the loop.  If it is greater that 7500
then
> > SET @<mynewstringname> = @SQLStr  and then SET @SQLStr  = ''.  This
should
> > give you 1500 possible in the SQL statement length.  If you need more
than
> > that you can repeat process by adding a third string variable and so
on...

> > Then EXEC(@str1 + @str2 + ...)

> > --
> > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.kara...@cornerstone.se>
> > wrote in message news:#FIBtYf3AHA.1264@tkmsftngp05...
> > > Steen,

> > > In general, you can use several variables, like:

> > > EXEC(@str1 + @str2).

> > > You'd have to consider whether that is doable for Darren's script...

> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > FAQ from Neil & others at: http://www.sqlserverfaq.com

> > > "Steen.Christiansen" <s...@3l.dk> wrote in message
> > news:eh3PxPe3AHA.2076@tkmsftngp07...
> > > > Hi Darren

> > > > Thats just what I was looking for. Thanks a lot.

> > > > I ran in to the problem that the variable @SQLStr is too small. I
know
> > that
> > > > the max size is 8000. Do you have any good ideas ?

> > > > Kind regards Steen

> > > > "Darren Brinksneader" <dbrinksnea...@atai.com> skrev i en meddelelse
> > > > news:eSisDeV3AHA.1440@tkmsftngp07...
> > > > > Here is a simple crosstab procedure with some sample tables and
> > executions
> > > > > following the definition.  You can run the script as-is, it cleans
> up
> > > > after
> > > > > itself.

> > > > > SET NOCOUNT ON
> > > > > GO
> > > > > CREATE PROC #pSimpleCrossTab
> > > > >   @TableName AS sysname,
> > > > >   @DistinctRow AS nvarchar(128),
> > > > >   @PivotColumn AS nvarchar(128),
> > > > >   @PivotData AS nvarchar(128) = NULL
> > > > > AS
> > > > > /* This Procedure works with SQL2000 and SQL Server 7.0 */

> > > > > DECLARE  @SQLStr varchar(8000), @COL VARCHAR(255)
> > > > > CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
> > > > > INSERT INTO #SCTTable
> > > > >  EXEC ('SELECT DISTINCT ' + @PivotColumn + ' FROM ' + @TableName)

> > > > > SELECT @SQLStr = 'SELECT DISTINCT ' + @DistinctRow
> > > > > SELECT @COL = MIN(PIV_COLS) FROM #SCTTable

> > > > >   WHILE @COL <= (SELECT MAX(PIV_COLS) FROM #SCTTable)
> > > > >    BEGIN
> > > > >     IF @PivotData IS NULL
> > > > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +
@PivotColumn
> +
> > '
> > > > >  FROM ' +  @TableName  + ' t2
> > > > > WHERE t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > > > >   AND CAST(' + @PivotColumn + ' as VARCHAR) = ''' + cast(@COL AS
> > > > > VARCHAR(255)) + ''' ) IS NOT NULL THEN ''YES'' ELSE ''NO'' END AS
'
> +
> > > > > @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > > > NVARCHAR),'.','_'),CHAR(32),'_'),':','')
> > > > >     ELSE --IF
> > > > >      SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +  @PivotData
+
> > > > > ' FROM ' +  @TableName  + ' t2
> > > > > WHERE  t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
> > > > >   AND  CAST(' + @PivotColumn + ' as VARCHAR) =  ''' + cast(@COL AS
> > > > > VARCHAR(255)) + ''' ) IS NULL THEN CHAR(32) ELSE ' + @PivotData +
'
> > END AS
> > > > '
> > > > > + @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
> > > > > NVARCHAR),'.','_'),CHAR(32),'_'),':','') + '_' + @PivotData
> > > > >      -- END IF
> > > > >       SELECT @COL = MIN(PIV_COLS) FROM #SCTTable WHERE PIV_COLS >
> @COL
> > > > >    END --WHILE

> > > > > SET @SQLStr = @SQLStr +  ' FROM ' + @tablename + ' t1'

> > > > > --PRINT @SQLStr --for DEBUGGING
> > > > > EXEC (@SQLStr)
> > > > > GO

> > > > > CREATE TABLE #Project (
> > > > > Project INT, Task INT, Start VARCHAR(8))

> > > > > INSERT INTO #Project VALUES (12345,  160, '1/1/01')
> > > > > INSERT INTO #Project VALUES (12345,  170,  '2/3/01')
> > > > > INSERT INTO #Project VALUES (98765,  160,  '1/9/02')
> > > > > INSERT INTO #Project VALUES (98765,  170,  '4/1/02')
> > > > > INSERT INTO #Project VALUES (98765,  180,  '5/1/02')
> > > > > GO

> > > > > #pSimpleCrossTab '#Project','Project','Task'
> > > > > GO
> > > > > #pSimpleCrossTab '#Project','Project','Task','start'
> > > > > GO

> > > > > CREATE TABLE #Comments
> > > > > (unit_id INTEGER NOT NULL,
> > > > >  comment_nbr INTEGER NOT NULL
> > > > >           CHECK (comment_nbr > 0),
> > > > >  comment VARCHAR(255) NOT NULL,
> > > > >  PRIMARY KEY (unit_id, comment_nbr));

> > > > > INSERT #Comments VALUES(1001,1,'Blah')
> > > > > INSERT #Comments VALUES(1001,2,'BlahBlah')
> > > > > INSERT #Comments VALUES(1002,1,'BlahBlah')
> > > > > INSERT #Comments VALUES(1002,2,'BlahBlahBlah')
> > > > > INSERT #Comments VALUES(1003,1,'BlahBlahBlah')
> > > > > INSERT #Comments VALUES(1003,2,'Blah')
> > > > > INSERT #Comments VALUES(1003,3,'BlahBlah')
> > > > > INSERT #Comments VALUES(1002,3,'BlahBlahBlah')
> > > > > INSERT #Comments VALUES(1002,4,'BlahBlahBlahBlahBlah')
> > > > > GO

> > > > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr'
> > > > > GO
> > > > > #pSimpleCrossTab '#Comments','unit_id','comment_nbr', 'comment'
> > > > > GO

> > > > > DROP TABLE #Comments
> > > > > DROP TABLE #Project
> > > > > DROP PROCEDURE #pSimpleCrossTab

> > > > > --
> > > > > Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> > > > > "Steen.Christiansen" <s...@3l.dk> wrote in message
> > > > > news:OmXKj5T3AHA.1692@tkmsftngp02...
> > > > > > Hi There

> > > > > > I'm trying to make an output that looks like :

> > > > > >                        Company1     Company2
> > > > > Company3...................
> > > > > > Item1                       0                    1
> > 1
> > > > > > Item2                        0                    0
> > 0
> > > > > > Item3                        1                    1
> > 1
> > > > > > .
> > > > > > .
> > > > > > .

> > > > > > The result shows that we are selling Item1 to Company2 an
> Company3.
> > > > Item2
> > > > > to
> > > > > > no one......

> > > > > > I have done the following SQL script, but I need two things :

> > > > > > The companyname in the headings, and to combine the results in
one
> > > > table.
> > > > > > The results I get looks like :

> > > > > >                 --------
> > > > > > Item1            0
> > > > > > Item2            0
> > > > > > Item3            0
> > > > > > .
> > > > > > .
> > > > > > .

> > > > > > and

> > > > > > --------
> > > > > >     1
> > > > > >     0
> > > > > >     1

> > > > > > Thanks to any help.

> > > > > > Kind regards Steen
> > > > > > s...@3L.DK

> > > > > > DECLARE
> > > > > > @company int

> > > > > > DECLARE
> > > > > > Company_Cursur CURSOR FOR
> > > > > > SELECT DISTINCT [Company no.] FROM V_Vare_Selskab_Matrix
> > > > > > ORDER BY [Company no.]

> > > > > > OPEN Company_Cursur

> > > > > > FETCH NEXT FROM Company_Cursur
> > > > > > INTO @company

> > > > > >  PRINT @company

> > > > > >  SELECT [Item No.],[NAME], (select [Company Name]FROM
> > > > > V_Vare_Selskab_Matrix
> > > > > > WHERE [Company no.] = @company AND [Item No.] = V.[Item No.]) AS
> > > > Company,
> > > > > >   ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE [Company
> no.]
> > =
> > > > > > @company AND [Item No.] = V.[Item No.]),0)

> > > > > >  FROM V_Vare_Selskab_Matrix V
> > > > > >  GROUP BY [Item No.], [NAME]
> > > > > >  ORDER BY [Item No.]

> > > > > >  FETCH NEXT FROM Company_Cursur
> > > > > >  INTO @company

> > > > > >  WHILE @@FETCH_STATUS = 0
> > > > > >  BEGIN

> > > > > >  PRINT @company

> > > > > >  SELECT
> > > > > >         ISNULL((SELECT Mark FROM V_Vare_Selskab_Matrix WHERE
> > [Company
> > > > no.]
> > > > > =
> > > > > > @company AND [Item No.] = V.[Item No.]),0)
> > > > > >  FROM V_Vare_Selskab_Matrix V
> > > > > >  GROUP BY [Item No.]
> > > > > >  ORDER BY [Item No.]
> > > > > >  FETCH NEXT FROM Company_Cursur
> > > > > >  INTO @company

> > > > > >  END
> > > > > >  CLOSE Company_Cursur

> > > > > > DEALLOCATE Company_Cursur