sql query help

  • hello everyone,

    i have a query with respect to pivot. As per my understanding pivot is used to transpose rows to columns. however as i was going through the documents and examples, the pivot works with a aggregate value. what if i don't want to do any aggregate ? what if there's any varchar value to be processed/represented as pivot ?

    please help.

    (apologies if its a stupid question)

  • For example? maybe check out Jeff Moden's article on Crosstabs

  • Can you use a MIN or MAX on one of the varchar columns?

    I also recommend reading Jeff's two articles on Pivots and Cross Tabs.

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

     

    businessday category itemno value
    2022-01-01 xyz banana 12.0 / $2.5
    2022-01-01 xyz apple 10.0 / $4
    2022-01-01 xyz fish 10.0
    2022-01-01 abc spinach 1.0 / $0.25
    2022-01-01 abc chicken 5.0

    End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:

    businessday category banana apple fish spinach chicken
    2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
    2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0
  • I think this will help ( but maybe not?! 🙂 ).  What I sometimes do is rather than using fully dynamic SQL, I use placeholder column names and then rename them prior to output (or in a final SELECT).  Sometimes static SQL is easier to deal with (and especially debug).

    /**** set up sample data ******************************************************/DROP TABLE IF EXISTS #data;
    CREATE TABLE #data (
    businessday date NOT NULL,
    category varchar(30) NULL,
    itemno varchar(50) NULL,
    value varchar(5000) NULL
    )
    INSERT INTO #data VALUES
    ('2022-01-21', 'xyz', 'banana', '12.0 / $2.5'),
    ('2022-01-21', 'xyz', 'apple', '10.0 / $4'),
    ('2022-01-21', 'xyz', 'fish', '10.0'),
    ('2022-01-21', 'abc', 'spinach', '1.0 / $0.25'),
    ('2022-01-21', 'abc', 'chicken', '5.0')
    --SELECT * FROM #data

    /**** code to produce wanted report *******************************************/DECLARE @item_counter int
    DECLARE @itemno varchar(50)
    DECLARE @sql nvarchar(max)
    DECLARE @start_date date
    SET @start_date = DATEADD(DAY, -7, GETDATE())

    DROP TABLE IF EXISTS #itemnos;
    --pre-create the table to:
    --(1) avoid keeping system tables locks while the table is loading
    --(2) allow a clustered index to be built *prior* to loading the table
    SELECT DISTINCT TOP (0)
    itemno, ROW_NUMBER() OVER(ORDER BY itemno) AS item_counter
    INTO #itemnos
    FROM #data
    WHERE businessday >= @start_date
    --
    CREATE UNIQUE CLUSTERED INDEX itemnos__CL
    ON #itemnos ( itemno )
    WITH ( FILLFACTOR = 100 );
    --
    INSERT INTO #itemnos
    SELECT DISTINCT
    itemno, ROW_NUMBER() OVER(ORDER BY itemno) AS item_counter
    FROM #data
    WHERE businessday >= @start_date

    DROP TABLE IF EXISTS #results;
    --pre-create the table to:
    --(1) avoid keeping system tables locks while the table is loading
    --(2) allow a clustered index to be built *prior* to loading the table
    SELECT DISTINCT TOP (0)
    businessday, category,
    MAX(CASE WHEN i.item_counter = 1 THEN value END) AS value1,
    MAX(CASE WHEN i.item_counter = 2 THEN value END) AS value2,
    MAX(CASE WHEN i.item_counter = 3 THEN value END) AS value3,
    MAX(CASE WHEN i.item_counter = 4 THEN value END) AS value4,
    MAX(CASE WHEN i.item_counter = 5 THEN value END) AS value5,
    MAX(CASE WHEN i.item_counter = 6 THEN value END) AS value6,
    MAX(CASE WHEN i.item_counter = 7 THEN value END) AS value7,
    MAX(CASE WHEN i.item_counter = 8 THEN value END) AS value8,
    MAX(CASE WHEN i.item_counter = 9 THEN value END) AS value9
    INTO #results
    FROM #data d
    INNER JOIN #itemnos i ON i.itemno = d.itemno
    WHERE businessday >= @start_date
    GROUP BY businessday, category
    --
    CREATE UNIQUE CLUSTERED INDEX data__CL
    ON #results ( businessday, category )
    WITH ( FILLFACTOR = 100 );
    --
    INSERT INTO #results
    SELECT
    businessday, category,
    MAX(CASE WHEN i.item_counter = 1 THEN value END) AS value1,
    MAX(CASE WHEN i.item_counter = 2 THEN value END) AS value2,
    MAX(CASE WHEN i.item_counter = 3 THEN value END) AS value3,
    MAX(CASE WHEN i.item_counter = 4 THEN value END) AS value4,
    MAX(CASE WHEN i.item_counter = 5 THEN value END) AS value5,
    MAX(CASE WHEN i.item_counter = 6 THEN value END) AS value6,
    MAX(CASE WHEN i.item_counter = 7 THEN value END) AS value7,
    MAX(CASE WHEN i.item_counter = 8 THEN value END) AS value8,
    MAX(CASE WHEN i.item_counter = 9 THEN value END) AS value9
    FROM #data d
    INNER JOIN #itemnos i ON i.itemno = d.itemno
    WHERE businessday >= @start_date
    GROUP BY businessday, category
    ORDER BY businessday, category

    DECLARE rename_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT itemno, item_counter
    FROM #itemnos
    ORDER BY item_counter

    OPEN rename_cursor;
    WHILE 1 = 1
    BEGIN
    FETCH NEXT FROM rename_cursor INTO @itemno, @item_counter
    IF @@FETCH_STATUS <> 0
    BREAK;
    SET @sql = 'EXEC tempdb.sys.sp_rename ''#results.value' +
    CAST(@item_counter AS varchar(3)) + ''', ''' + @itemno + ''', ''COLUMN'''
    EXEC(@sql)
    END /*WHILE*/CLOSE rename_cursor;
    DEALLOCATE rename_cursor;

    SELECT *
    FROM #results
    ORDER BY businessday, category

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • sqlenthu 89358 wrote:

    I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

    businessday category itemno value
    2022-01-01 xyz banana 12.0 / $2.5
    2022-01-01 xyz apple 10.0 / $4
    2022-01-01 xyz fish 10.0
    2022-01-01 abc spinach 1.0 / $0.25
    2022-01-01 abc chicken 5.0

    End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:

    businessday category banana apple fish spinach chicken
    2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
    2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0

    Are the itemno-based columns in the end result in any particular order?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    sqlenthu 89358 wrote:

    I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

    businessday category itemno value
    2022-01-01 xyz banana 12.0 / $2.5
    2022-01-01 xyz apple 10.0 / $4
    2022-01-01 xyz fish 10.0
    2022-01-01 abc spinach 1.0 / $0.25
    2022-01-01 abc chicken 5.0

    End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:

    businessday category banana apple fish spinach chicken
    2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
    2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0

    Are the itemno-based columns in the end result in any particular order?

    in naming order:

    order by itemno ASC

  • sqlenthu 89358 wrote:

    I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

    businessday category itemno value
    2022-01-01 xyz banana 12.0 / $2.5
    2022-01-01 xyz apple 10.0 / $4
    2022-01-01 xyz fish 10.0
    2022-01-01 abc spinach 1.0 / $0.25
    2022-01-01 abc chicken 5.0

    End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:

    businessday category banana apple fish spinach chicken
    2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
    2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0

    If you actually want coded help, then you need to look at one more article and that would be the first article in my signature line below.  You've been around long enough to know this.  Do it now. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Obviously there's no need for nullable columns or a cursor

    drop table if exists #data;
    go
    create table #data (
    businessday date not null,
    category varchar(30) not null,
    itemno varchar(50) not null,
    [value] varchar(5000) not null);

    insert into #data(businessday, category, itemno, [value]) values
    ('2022-01-21', 'xyz', 'banana', '12.0 / $2.5'),
    ('2022-01-21', 'xyz', 'apple', '10.0 / $4'),
    ('2022-01-21', 'xyz', 'fish', '10.0'),
    ('2022-01-21', 'def', 'spinach', '1.0 / $0.25'),
    ('2022-01-21', 'def', 'chicken', '5.0');
    --select * from #data

    declare
    @sql_prefix nvarchar(max)=N'select businessday, category, ',
    @sql_suffix nvarchar(max)=N' from #data group by businessday, category order by businessday, category desc;',
    @pvt_prefix nvarchar(max)=N'max(case when itemno=''',
    @pvt_middle nvarchar(max)=N''' then [value] else ''0'' end) [',
    @pvt_suffix nvarchar(max)=N']',
    @sql nvarchar(max);

    with
    unq_itemno_cte(itemno) as (
    select distinct itemno
    from #data),
    pvt_cte(itemno, string) as (
    select itemno, concat(@pvt_prefix,
    itemno,
    @pvt_middle,
    itemno,
    @pvt_suffix)
    from unq_itemno_cte)
    select @sql=concat(@sql_prefix,
    string_agg(string, N',') within group (order by itemno),
    @sql_suffix)
    from pvt_cte;

    print(@sql);

    /* generates the following code (when formatted) *//*
    select businessday, category,
    max(case when itemno='apple' then [value] else '0' end) [apple],
    max(case when itemno='banana' then [value] else '0' end) [banana],
    max(case when itemno='chicken' then [value] else '0' end) [chicken],
    max(case when itemno='fish' then [value] else '0' end) [fish],
    max(case when itemno='spinach' then [value] else '0' end) [spinach]
    from #data
    group by businessday, category
    order by businessday, category desc;
    */

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • STRING_AGG is not available in SQL Server 2016.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    STRING_AGG is not available in SQL Server 2016.

    Maybe try starting here for examples of the old way

    One modern element I would add would be to use CONCAT_WS ("concat with separator") instead of CONCAT in the outer SELECT.  That way the trailing space in @pvt_prefix and the leading space in @sql_suffix could be replaced with a single space as the separator

    declare
    @sql_prefix nvarchar(max)=N'select businessday, category,',
    @sql_suffix nvarchar(max)=N'from #data group by businessday, category order by businessday, category desc;',
    @pvt_prefix nvarchar(max)=N'max(case when itemno=''',
    @pvt_middle nvarchar(max)=N''' then [value] else ''0'' end) [',
    @pvt_suffix nvarchar(max)=N']',
    @sql nvarchar(max);

    with
    unq_itemno_cte(itemno) as (
    select distinct itemno
    from #data),
    pvt_cte(itemno, string) as (
    select itemno, concat(@pvt_prefix,
    itemno,
    @pvt_middle,
    itemno,
    @pvt_suffix)
    from unq_itemno_cte)
    select @sql=concat_ws(' ', @sql_prefix,
    string_agg(string, N',') within group (order by itemno),
    @sql_suffix)
    from pvt_cte;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • ScottPletcher wrote:

    STRING_AGG is not available in SQL Server 2016.

    Something like this maybe

    with
    unq_itemno_cte(itemno) as (
    select distinct itemno
    from #data),
    pvt_cte(itemno, string) as (
    select itemno, concat(@pvt_prefix,
    itemno,
    @pvt_middle,
    itemno,
    @pvt_suffix)
    from unq_itemno_cte)
    select @sql=concat(@sql_prefix,
    stuff((select concat(', ', string)
    from pvt_cte
    order by itemno
    for xml path('')),1,1,''),
    @sql_suffix);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Again... if you post some sample data in a readily consumable format, we can help a whole lot more.  See the first link in my signature line below for one of a couple different ways to do so.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Again... if you post some sample data in a readily consumable format, we can help a whole lot more.  See the first link in my signature line below for one of a couple different ways to do so.

     

    Here you go Jeff:

    --===== If the test table already exists, drop it
    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
    DROP TABLE #mytable

    --===== Create the test table with
    CREATE TABLE #mytable
    (
    businessday DATETIME,
    ColumnName varchar(20),
    ColumnView varchar(50),
    ColToPivot varchar(20),
    ColValue varchar(30)
    )

    --===== Insert the data
    INSERT into #mytable
    SELECT '2022-01-01', 'ABC', 'DIM_VIEW', 'BUSINESS_RISK', '2.35' UNION ALL
    SELECT '2022-01-01', 'ABC', 'DIM_VIEW', 'MARKET CRED', '1.35 / $2.40' UNION ALL
    SELECT '2022-01-01', 'ABC', 'DIM_VIEW', 'MONETAY FUND', '4.80' UNION ALL
    SELECT '2022-01-01', 'ABC', 'APP_VIEW', 'BUSINESS_RISK', '0.95' UNION ALL
    SELECT '2022-01-01', 'DEF', 'DIM_VIEW', 'BUSINESS_RISK', '1.15 / $0.90' UNION ALL
    SELECT '2022-01-01', 'DEF', 'DIM_VIEW', 'MONETARY FUND', '71.70 / $122.47' UNION ALL
    SELECT '2022-01-01', 'DEF', 'APP_VIEW', 'BUSINESS_RISK', '5.60' UNION ALL
    SELECT '2022-01-01', 'GHK', 'APP_VIEW', 'BUSINESS_RISK', '3.85 / $10.08'

    And here is the the result I need:

    businessdayColumnNameColumnViewBUSINESS_RISKMARKET CREDMONETARY FUND
    2022-01-01ABCDIM_VIEW2.351.35 / $2.404.80
    2022-01-01ABCAPP_VIEW0.95nullnull
    2022-01-01DEFDIM_VIEW1.15 / $0.90null71.70 / $122.47
    2022-01-01DEFAPP_VIEW5.60nullnull
    2022-01-01GHKAPP_VIEW3.85 / $10.08nullnull

    The pivot columns can vary from week to week. Its for a weekly report. It will be for 7 business days at a time.

  • Oops, I edited the code above and posted instead grr.  Preemptively, imo concatenation is preferable to replacement when the strings are not complicated (such as this one here) because it "goes with the flow"

    • This reply was modified 3 months, 4 weeks ago by  Steve Collins.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply