Select Query needed to create a simple Pivot or Crosstab

[Craig Cox]

I have a table of sample results in Firebird 2.5.  One column contains the names of the samples, one column contains the names of chemicals and another column contains the concentrations.  

For example 

Sample       Chemical    Concentration

Sample1      Calcium                 5

Sample2      Calcium                 8

Sample3      Calcium                 1

Sample1      Sodium                10

Sample2      Sodium                12

Sample3      Sodium                13

Sample1      Chloride             100

Sample2      Chloride             110

Sample3      Chloride             120

How do I write a query to get create columns based on the chemical names that contain their respective concentrations? 

Sample        Calcium     Sodium    Chloride

Sample1            5                 10           100

Sample2            8                 12           110

Sample3            1                 13           120

Thanks

[Lucas Schatz]

You could do like this:

select sample, sum(iif(chem='Sd',con,0)) as sd, sum(iif(chem='Ca',con,0)) as ca, sum(iif(chem='Ch',con,0)) as ch from (
select 1 as sample, 'Sd' as chem, 5 as con from rdb$database
union all
select 2 as sample, 'Ca' as chem, 6 as con from rdb$database
union all
select 1 as sample, 'Ch' as chem, 2 as con from rdb$database
union all
select 1 as sample, 'Sd' as chem, 3 as con from rdb$database
)
group by 1

[Svein Erling Tysvær]

I don't know of any simple way to turn rows into columns with Firebird 2.5 (how could you possibly prepare a statement when you do not know the name or number of columns at the time of prepare?). I think it is possible to do things in two steps, where the first step would involve querying the table to build another query. Then the second step would be to execute this second query. However, this is cumbersome (and with unknown columns it cannot be done in a stored procedure).

Whenever I need pivot tables, I sum the results in Firebird and then use Excel to transform things into pivot tables. If you use a programming language, you may find that this language have components that can be used for making pivot tables.

Sorry, but the concept of pivot tables are easy to grasp for a human, but far from simple for SQL (at least not the type of SQL that is implemented in Firebird 2.5).

Set

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/cb813429-c126-4e27-9d05-7943ccf9eb51n%40googlegroups.com.

[Stanislav Hruška]

From internet: Post with five methods for converting rows to columns.

From the classic ‘CASE’ construction to the newer PIVOT and ROW_NUMBER() OVER (ORDER BY) functions.

Be careful!

Some methods presented here can have a severe negative impact on performance. Especially the in-line function with a cursor should be used with the utmost precaution.Check the query plan and the statistics i/o (see this post) of your queries before putting the code into your production environment!

1. use a CASE statement

SELECT DATEPART(YEAR,orderdate),

    SUM(CASE WHEN DATEPART(q,orderdate) = 1 THEN orderamount ELSE 0 END) AS Qtr1,

    SUM(CASE WHEN DATEPART(q,orderdate) = 2 THEN orderamount ELSE 0 END) AS Qtr2,

    SUM(CASE WHEN DATEPART(q,orderdate) = 3 THEN orderamount ELSE 0 END) AS Qtr3,

    SUM(CASE WHEN DATEPART(q,orderdate) = 4 THEN orderamount ELSE 0 END) AS Qtr4,

    SUM(orderamount) AS Total

 FROM Orders

-- additional where clause goes here...

GROUP BY DATEPART(YEAR,orderdate)

2. use the COALESCE function

DECLARE @AllValues VARCHAR(4000)

SELECT @AllValues = COALESCE(@AllValues + ',', '') + HandlingCode

FROM OrdersDetails

WHERE OrderNumber = @OrderNumber

3. use ROW_NUMBER() OVER (ORDER BY)

SELECT OrderNumber, OrderDate,

--get the special handling codes and show them as columns, max of 3 (agreed by users)

    (SELECT HandlingCode

        FROM

        (

          SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode

          FROM OrdersDetails

          WHERE OrdersDetails.OrderNumber = Orders.OrderNumber

        ) HandlingCode

        WHERE ROWNUMBER = 1) HandlingCode1,

    (SELECT HandlingCode

        FROM

        (

          SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode

          FROM OrdersDetails

          WHERE OrdersDetails.OrderNumber = Orders.OrderNumber

        ) HandlingCode

        WHERE ROWNUMBER = 2) HandlingCode2,

    (SELECT HandlingCode

        FROM

        (

          SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode

          FROM OrdersDetails

          WHERE OrdersDetails.OrderNumber = Orders.OrderNumber

        ) HandlingCode

        WHERE ROWNUMBER = 3) HandlingCode3

FROM Orders

WHERE OrderNumber = @OrderNumber

4. Use an inline function

SELECT OrderNumber, OrderDate, fn_GetHandlingCodes (OrderNumber)

FROM Orders

WHERE OrderNumber = @OrderNumber

CREATE FUNCTION dbo.fn_GetHandlingCodes (@OrderNumber INT)

RETURNS VARCHAR(1200)

AS

BEGIN

   DECLARE @HandlingCode VARCHAR(20)

   DECLARE @ReturnValue  VARCHAR(4000)

-- use that fastest cursor methods: local fast_forward

   DECLARE code_cursor CURSOR LOCAL fast_forward FOR

    SELECT HandlingCode

     FROM OrdersDetails

    WHERE OrderNumber = @OrderNumber

      AND NOT HandlingCode IS NULL -- filled

   SET @ReturnValue = ''  -- set to non null

   OPEN code_cursor

   FETCH NEXT FROM code_cursor  INTO @HandlingCode

   WHILE (@@FETCH_STATUS = 0)

   BEGIN

       SET @ReturnValue = @ReturnValue + @HandlingCode + ', '

       IF LEN (@ReturnValue) > 1000 BREAK -- avoid overflow

       FETCH NEXT FROM code_cursor INTO @HandlingCode

   END

   CLOSE code_cursor

   DEALLOCATE code_cursor

-- remove last delimiter

   IF LEN(@ReturnValue) > 1 SET @ReturnValue = SUBSTRING(@ReturnValue,1,LEN(@ReturnValue)-2)

   RETURN @ReturnValue

END

5. Use a pivot

USE AdventureWorks

GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5

FROM

(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt

ORDER BY VendorID

Dátum: pondelok 19. júla 2021, čas: 23:28:35 UTC+2, odosielateľ: Set

[Ivan Petroff]

I made it in two steps:

1) Used a DDL query to create a table for pivot data using data from DML SQL query for column names, in my case years of sampling

CREATE GLOBAL TEMPORARY TABLE GTT_PIVOT (

    ID         D_INTKEY /* D_INTKEY = INTEGER NOT NULL */,

    SEX        D_SEX /* D_SEX = CHAR(1) DEFAULT 'ж' CHECK (value in ('м', 'ж')) */,

    BIRTHYEAR  D_INTKEY /* D_INTKEY = INTEGER NOT NULL */,

    "1975"     FLOAT default 0,

    "1976"     FLOAT default 0,

    "1977"     FLOAT default 0,

    "1978"     FLOAT default 0,

    "1979"     FLOAT default 0,

    "1980"     FLOAT default 0,

    "1981"     FLOAT default 0,

    "1982"     FLOAT default 0,

    "1983"     FLOAT default 0,

    "1984"     FLOAT default 0,

    "1985"     FLOAT default 0,

....

2) Populate this table with data from source table in 'for select' cycle using execute statement with YEAR value for column name:

      stmt = 'update or insert into gtt_pivot'

        || ' (id, Sex, BIRTHyear, "' || cast( YEAR as varchar(4)) || '")'

        || ' VALUES (' || cast( ID as varchar(4))

        || ', ' || '''' || :sex || ''''

        || ', ' || cast( BIRTHyear  as varchar(4))

        || ', ' || cast( SAMPLEVALUE as varchar(7)) || ')'

        || ' MATCHING (id);';

      if (stmt is not null) then

        execute statement stmt ;

Between steps 1 and 2 one must reconnect for metadata update

[Ivan Petroff]

Today I had a look on the code and checked it. It works all right.

I tested it on my data, but it would not be hard to apply it to any data.

--Source table with data to pivot

 CREATE GLOBAL TEMPORARY TABLE GTT_SOURCE (

    ID         INTEGER NOT NULL,

    SEX        CHAR(1) NOT NULL,

    BIRTHYEAR  INTEGER NOT NULL,

    Y          INTEGER NOT NULL,

    SAMPLE     FLOAT

) ON COMMIT PRESERVE ROWS;

 --Create pivot table

 execute block

as

declare variable y integer;

declare variable stmt varchar(4096);

begin

  stmt = 'CREATE GLOBAL TEMPORARY TABLE GTT_PIVOT ('||

    'ID INTEGER NOT NULL,'||

    'SEX CHAR(1) NOT NULL,'||

    'BIRTHYEAR INTEGER NOT NULL';

  for select distinct

    gtt_source.y

  from gtt_source

  order by 1

  into

    :y

  do

    stmt = stmt || ',y' || cast( y as varchar(4))|| ' float default 0';

  stmt = stmt||') ON COMMIT PRESERVE ROWS;';

  execute statement stmt;

end

 

--Pivot table created by code above

 CREATE GLOBAL TEMPORARY TABLE GTT_PIVOT (

ID INTEGER NOT NULL,

SEX CHAR(1) NOT NULL,

BIRTHYEAR INTEGER NOT NULL,

y1961 float default 0,

y1962 float default 0,

y1963 float default 0,

y1964 float default 0,

...

y2018 float default 0,

y2019 float default 0,

y2020 float default 0,

y2021 float default 0

) ON COMMIT PRESERVE ROWS;

 

--Code to populate pivot table with source data

 execute block

as

declare variable Y integer;

declare variable ID integer;

declare variable SEX char(1);

declare variable BIRTHyear integer;

declare variable sample FLOAT;

declare variable stmt varchar(4096);

begin

    stmt = '';

    for select

      gtt_source.y,

      gtt_source.id,

      gtt_source.sex,

      gtt_source.birthyear,

      gtt_source.sample

    from gtt_source

    into

      :y,

      :id,

      :sex,

      :birthyear ,

      :sample

    do begin

      if (sample is not null) then begin

        stmt = 'update or insert into gtt_pivot'

        || ' (id, sex, birthyear, y' || cast( y as varchar(4)) || ')'

        || ' values (' || cast( id as varchar(4))

        || ', ' || '''' || :sex || ''''

        || ', ' || cast( birthyear  as varchar(4))

        || ', ' || cast( sample  as varchar(10)) || ')'

        || ' matching (id, sex, birthyear);';

        execute statement stmt;

      end

    end

end