Professional Web Applications Themes

Crosstab query - Nearly there! - Microsoft SQL / MS SQL Server

Try: select a.status, (select count(*) from #crosstab where tsize = 20 and status =a.status) [20], (select count(*) from #crosstab where tsize = 40 and status =a.status) [40] from (select 'loaded' status union all select 'empty' status) a -- -Vishal "Chris Strug" <hotmailsolace1884.com> wrote in message news:%VgOa.4346$w17.33866772news-text.cableinet.net... > Hi, > > I'm trying to te an Access crosstab query in SQL Server 2000 and > while I think I have the basic concept, I can seem to get the execution > right. > > The steps I have taken so far are: > > I have a table, containing rather a ...

  1. #1

    Default Re: Crosstab query - Nearly there!

    Try:

    select a.status,
    (select count(*) from #crosstab where tsize = 20
    and status =a.status) [20],
    (select count(*) from #crosstab where tsize = 40
    and status =a.status) [40]
    from
    (select 'loaded' status
    union all
    select 'empty' status) a


    --
    -Vishal

    "Chris Strug" <hotmailsolace1884.com> wrote in message
    news:%VgOa.4346$w17.33866772news-text.cableinet.net...
    > Hi,
    >
    > I'm trying to te an Access crosstab query in SQL Server 2000 and
    > while I think I have the basic concept, I can seem to get the execution
    > right.
    >
    > The steps I have taken so far are:
    >
    > I have a table, containing rather a lot of data. I have taken the required
    > data (adjusting it on the way) and inserted it into a temporary table
    > (#stripped).
    >
    > Now, I'm trying to create the crosstab by using another temporary table
    > (#crosstab). As I understand it I have to insert the data in a field by
    > field basis.
    >
    > The structure of my temporary table is thus:
    >
    > MovementNo | Status | TSize
    > ------------------------------------------------------------
    > 12345678 | Empty | 20
    > 23456789 | Empty | 40
    > 34567890 | Loaded | 20
    > 45678901 | Loaded | 20
    > .
    > .
    > .
    > .
    >
    > The layout of my crosstab table should be:
    >
    > Status | 20 | 40
    > ----------------------------------------------------------------
    > Loaded | 345 | 349
    > Empty | 89 | 562
    >
    > Where the numbers are the count of "MovementNo" for each particular size
    (20
    > or 40) and type (loaded or empty).
    >
    > Now the headers and fields never change (always 20 / 40 and loaded /
    empty),
    > however, I can't seem to get the final part to create the data.
    >
    > What do I need to include after the final INSERT in my code (below).
    >
    > Any advice or guidance is gratefully received.
    >
    > Kind thanks
    >
    > Chris Strug
    >
    > Code Section - Start
    > *********************************
    >
    > CREATE PROCEDURE SPC_TEU
    >
    > As
    >
    > CREATE TABLE #stripped
    > (
    > MovementNo int NULL,
    > Status nvarchar(15),
    > TSize nvarchar(10)
    > )
    >
    > CREATE TABLE #CrossTab
    > (
    > Status nvarchar(10),
    > Twenty int null,
    > forty int null
    > )
    >
    > INSERT #stripped(MovementNo,Status,TSize)
    > SELECT TOP 100 PERCENT dbo.STOCK.MovementNo,
    > case when left(dbo.STOCK.Status,5) = 'Empty' then 'Empty' ELSE 'Loaded'
    > END,
    > case when dbo.CONTAINER.[Size] like '10_' then '20'
    > when dbo.container.[Size] like '20_' then '20'
    > when dbo.container.[size] like '30_' then '40'
    > when dbo.container.[Size] like '40_' then '40'
    > else '40'
    > end
    > FROM dbo.CONTAINER INNER JOIN
    > dbo.STOCK ON dbo.CONTAINER.[Container No] =
    > dbo.STOCK.ContainerNo
    > WHERE (dbo.stock.dateout is null)
    > ORDER BY dbo.STOCK.Status, dbo.CONTAINER.[Size]
    >
    > INSERT #CrossTab(Status,twenty,forty)
    > --select * from #stripped
    >
    > GO
    >
    >

    Vishal Parkar Guest

  2. #2

    Default Re: Crosstab query - Nearly there!

    Chris,

    There is no need for temp table...

    CREATE PROCEDURE SPC_TEU
    AS
    SELECT Status,
    [20]=sum(case when [Size]=20 then 1 else 0 end),
    [40]=sum(case when [Size]=40 then 1 elese 0 end)
    FROM(
    SELECT [Status]=case when left(dbo.STOCK.Status,5) = 'Empty' then 'Empty'
    ELSE 'Loaded' END,
    [Size]= case when dbo.CONTAINER.[Size] like '[1-2]0_' then 20 else 40 end
    FROM dbo.CONTAINER INNER JOIN dbo.STOCK ON dbo.CONTAINER.[Container
    No]=dbo.STOCK.ContainerNo
    WHERE (dbo.stock.dateout is null)) derived
    GROUP BY Status
    GO

    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]


    "Chris Strug" <hotmailsolace1884.com> wrote in message
    news:%VgOa.4346$w17.33866772news-text.cableinet.net...
    > Hi,
    >
    > I'm trying to te an Access crosstab query in SQL Server 2000 and
    > while I think I have the basic concept, I can seem to get the execution
    > right.
    >
    > The steps I have taken so far are:
    >
    > I have a table, containing rather a lot of data. I have taken the required
    > data (adjusting it on the way) and inserted it into a temporary table
    > (#stripped).
    >
    > Now, I'm trying to create the crosstab by using another temporary table
    > (#crosstab). As I understand it I have to insert the data in a field by
    > field basis.
    >
    > The structure of my temporary table is thus:
    >
    > MovementNo | Status | TSize
    > ------------------------------------------------------------
    > 12345678 | Empty | 20
    > 23456789 | Empty | 40
    > 34567890 | Loaded | 20
    > 45678901 | Loaded | 20
    > .
    > .
    > .
    > .
    >
    > The layout of my crosstab table should be:
    >
    > Status | 20 | 40
    > ----------------------------------------------------------------
    > Loaded | 345 | 349
    > Empty | 89 | 562
    >
    > Where the numbers are the count of "MovementNo" for each particular size
    (20
    > or 40) and type (loaded or empty).
    >
    > Now the headers and fields never change (always 20 / 40 and loaded /
    empty),
    > however, I can't seem to get the final part to create the data.
    >
    > What do I need to include after the final INSERT in my code (below).
    >
    > Any advice or guidance is gratefully received.
    >
    > Kind thanks
    >
    > Chris Strug
    >
    > Code Section - Start
    > *********************************
    >
    > CREATE PROCEDURE SPC_TEU
    >
    > As
    >
    > CREATE TABLE #stripped
    > (
    > MovementNo int NULL,
    > Status nvarchar(15),
    > TSize nvarchar(10)
    > )
    >
    > CREATE TABLE #CrossTab
    > (
    > Status nvarchar(10),
    > Twenty int null,
    > forty int null
    > )
    >
    > INSERT #stripped(MovementNo,Status,TSize)
    > SELECT TOP 100 PERCENT dbo.STOCK.MovementNo,
    > case when left(dbo.STOCK.Status,5) = 'Empty' then 'Empty' ELSE 'Loaded'
    > END,
    > case when dbo.CONTAINER.[Size] like '10_' then '20'
    > when dbo.container.[Size] like '20_' then '20'
    > when dbo.container.[size] like '30_' then '40'
    > when dbo.container.[Size] like '40_' then '40'
    > else '40'
    > end
    > FROM dbo.CONTAINER INNER JOIN
    > dbo.STOCK ON dbo.CONTAINER.[Container No] =
    > dbo.STOCK.ContainerNo
    > WHERE (dbo.stock.dateout is null)
    > ORDER BY dbo.STOCK.Status, dbo.CONTAINER.[Size]
    >
    > INSERT #CrossTab(Status,twenty,forty)
    > --select * from #stripped
    >
    > GO
    >
    >

    oj Guest

  3. #3

    Default Re: Crosstab query - Nearly there!

    Oj,

    Thanks for your reply. I thought it may have been possible to skip the temp
    table (#stripped) but as I'm very new to T-SQL I wanted to keep my example
    as simple as possible.

    However, if I may ask, I don't follow what the "derived" keyword does in the
    last line. I've had a quick look in BOL and I can't see anything that that
    explains it. There also appears to be an syntax error at this point -
    although to be honest I haven't looked at your code in too much depth - it's
    probably something as simple as a missing "("

    However, thanks for your kind reply.

    Many thanks

    Chris S

    "oj" <nospam_ojngohome.com> wrote in message
    news:eq$gLUKRDHA.1712TK2MSFTNGP12.phx.gbl...
    > Chris,
    >
    > There is no need for temp table...
    >
    > CREATE PROCEDURE SPC_TEU
    > AS
    > SELECT Status,
    > [20]=sum(case when [Size]=20 then 1 else 0 end),
    > [40]=sum(case when [Size]=40 then 1 elese 0 end)
    > FROM(
    > SELECT [Status]=case when left(dbo.STOCK.Status,5) = 'Empty' then 'Empty'
    > ELSE 'Loaded' END,
    > [Size]= case when dbo.CONTAINER.[Size] like '[1-2]0_' then 20 else 40 end
    > FROM dbo.CONTAINER INNER JOIN dbo.STOCK ON dbo.CONTAINER.[Container
    > No]=dbo.STOCK.ContainerNo
    > WHERE (dbo.stock.dateout is null)) derived
    > GROUP BY Status
    > GO
    >
    > --
    > -oj
    > RAC v2.2 & QALite!
    > [url]http://www.rac4sql.net[/url]
    >
    >


    Chris Strug Guest

  4. #4

    Default Re: Crosstab query - Nearly there!

    > However, if I may ask, I don't follow what the "derived" keyword does in
    the

    DERIVED isn't a keyword. It's just an alias for the derived table (the query
    in brackets following FROM). An alias is required for dervied tables but any
    name will do. The name "xyz" would be just as valid as "derived".

    > explains it. There also appears to be an syntax error at this point -
    The syntax error is just the typo "elese" instead of "ELSE":

    ....
    [40]=sum(case when [Size]=40 then 1 else 0 end)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

Similar Threads

  1. Crosstab Query Export
    By PrinBD in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 17th, 05:41 PM
  2. Crosstab Query with Date Fields
    By mostlySimple in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 5th, 04:28 AM
  3. Crosstab query with multiple value fields
    By melody in forum ASP Database
    Replies: 1
    Last Post: October 20th, 01:02 AM
  4. Creatin a Datasheet Form that looks like CrossTab Query?
    By Keith Christmas in forum Microsoft Access
    Replies: 2
    Last Post: July 19th, 08:25 AM
  5. No true Crosstab Query in Oracle???
    By damorgan in forum Oracle Server
    Replies: 0
    Last Post: December 30th, 05:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139