Forum Replies Created

Viewing 15 posts - 1,906 through 1,920 (of 2,171 total)

  • RE: Need to PIVOT without aggregate function

    Yes, I know. We all hoped that the new SQL 2005 PIVOT operator really should be a PIVOT, and not just a rewrite for a lot of case statements...

    But who...

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 3:40 pm

    #651492

  • RE: Need to PIVOT without aggregate function

    And if you have several tables like tblProperties, just add a UNION after the red text in my previous rewritten SP.

    SELECT      Aircraft,

                PropertyName,

                Value

    FROM        tblProperties

    UNION

    SELECT      Aircraft,

               ...

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 3:38 pm

    #651490

  • RE: Need to PIVOT without aggregate function

    Using this slighty rewritten SP from my article will do the trick for you. It uses no aggregations!

    CREATE PROCEDURE uspAirplaneProperties

    AS

    SET NOCOUNT ON

    CREATE TABLE #Aggregates

                ...

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 3:34 pm

    #651488

  • RE: Need to PIVOT without aggregate function

    You can not use PIVOT for SQL 2005 since you need to hardwire the columns.

    Your choice of method is dynamic SQL.

    And selecting MAX of 1 record is very fast!

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 3:25 pm

    #651486

  • RE: Stored procedure for Median

    For the sample data provided, is this the wanted output?

    Type of Request  Priority  Median

    ---------------  --------  ------

    KCR Request             1     5.0

    KCR Request             2     5.0

    KCR Request             3     5.5

    MPR Request             1     2.0

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 2:59 pm

    #651480

  • RE: Need to PIVOT without aggregate function

    Bruce might mean an ordinary transform of a resultset. Changing rows to columns and columns to rows.

     

    Take a moment or two and read my recent article here

    http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

    That...

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 2:40 pm

    #651477

  • RE: Insert Statements

    Use star sign

     

    select *

    or

    insert into sometable

    select *

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 10:29 am

    #651397

  • RE: Calculate Z-Score

    Try begin with STDEV and STDEVP functions and see what they do for you.

     

    An example of calculating z-score is

    zscore = (product category sales - product avg) / product stdev

    Meaning

    zscore =...

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 10:06 am

    #651387

  • RE: cannot select into

    It is @ProgramID and @CustomerID that is missing an alias!

    SELECT @ProgramID AS ProgramID,

           @CustomerID AS CustomerID,

           P.PaymentTransactionID,

           P.Period, 

           P.PaymentTypeID, 

           P.CurrencyCode,

           P.GLAccountID,

           Amount = convert(decimal (12,2), (P.AmountUSD /...

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 6:00 am

    #651302

  • RE: counting common values of a field within super-fields

    This will produce only the pairs where there are matches, which will be much smaller resultset that previous

    -- prepare test data

    declare @table table (s int, item int)

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 3:48 am

    #651282

  • RE: counting common values of a field within super-fields

    Will this code do?

    -- prepare test data

    declare @table table (s int, item int)

    insert @table

    select 1, 1 union all

    select 1, 2 union all

    select 1, 3 union all

    select 2, 2 union all

    select 3, 5

    N 56°04'39.16"
    E 12°55'05.25"

    July 25, 2006 at 2:55 am

    #651268

  • RE: Need help on calculation query

    -- Prepare test data

    declare @test table (Location2 varchar(7), Measure_Name varchar(8), Location1 varchar(7), Measure_Value smallmoney)

    insert @test

    select 'S00152', 'TY_Sales', 'Chicago', 2111.05 union all

    select 'S00152', 'PY_Sales', 'Chicago', 2200.65 union all

    select...

    N 56°04'39.16"
    E 12°55'05.25"

    July 24, 2006 at 2:26 pm

    #651189

  • RE: generic db role (probably the oldest question in the book)

    You have to explicit set GRANT on every SP you want the user to have acccess to.

    N 56°04'39.16"
    E 12°55'05.25"

    July 24, 2006 at 11:23 am

    #651151

  • RE: Complex Query

    -- Prepare test data

    declare @table table (seq tinyint, area smallint, page tinyint)

    insert @table

    select 1, 300, 0 union all

    select 1, 200, 0 union all

    select 1, 100, 0...

    N 56°04'39.16"
    E 12°55'05.25"

    July 24, 2006 at 10:00 am

    #651125

  • RE: To find invalid product codes

    SELECT  Code,

      COUNT(*)

    FROM  Table

    GROUP BY Code

    HAVING  COUNT(*) > 1

    N 56°04'39.16"
    E 12°55'05.25"

    July 24, 2006 at 9:47 am

    #651112

    • Viewing 15 posts - 1,906 through 1,920 (of 2,171 total)