locked
Query RRS feed

  • Question

  • User-797751191 posted

    Hi

       I want to display data like below is the link attached

    https://imgur.com/QPzkzkO

    Thanks

    Tuesday, March 3, 2020 1:45 PM

All replies

  • User475983607 posted

    jsshivalik

    Hi

       I want to display data like below is the link attached

    https://imgur.com/QPzkzkO

    Thanks

    And where is your code?  Are you receiving an error?  Are you building an Excel file?  If so, what Excel library are you using?  Have you tried reading the documentation?

    Tuesday, March 3, 2020 2:12 PM
  • User-1330468790 posted

    Hi, jsshivalik,

    I presume that you will fetch the data from database. 

    Using PIVOT can populate data into different columns that you have set in advance.

    Moreover, you problem can be defined as "Dynamically PIVOT" that you can use it as searching keyword if you want to learn more.

    More details, you can refer to below sql script

    DECLARE @Columns as VARCHAR(MAX)
    SELECT @Columns = COALESCE(@Columns + ',','') + QUOTENAME(DATENAME(yyyy, [Month])+'-'+ Cast(DATEPart(mm,[Month]) as varchar(2))+ ' Qty' )+',' + QUOTENAME(DATENAME(yyyy, [Month])+'-'+ Cast(DATEPart(mm,[Month]) as varchar(2))+ ' Amount' )
    FROM (SELECT DISTINCT MONTH FROM [Items])DT
    DECLARE @sql as VARCHAR(MAX)
    
    SET @sql = '
    SELECT * FROM
    (SELECT  [Item], 
    [Description],
    DATA,
    DateName( year , [Month]) + ''-'' +Cast(DATEPart(mm,[Month]) as varchar(2)) + '' '' + COLUMN_NAME AS PIV_COL
    FROM Items cross apply (VALUES (''AMOUNT'',Amount), (''Qty'',Qty) ) CS(COLUMN_NAME, DATA) ) a PIVOT (sum(DATA) FOR PIV_COL IN( '+ @Columns +')) PV ORDER BY item;'; print(@sql) Exec(@sql);

    DataBase:

    CREATE TABLE [dbo].[Items] (
        [Id]          INT           IDENTITY (1, 1) NOT NULL,
        [Month]       DATETIME      NOT NULL,
        [Item]        INT           NOT NULL,
        [Description] NVARCHAR (50) NOT NULL,
        [Qty]         INT           NOT NULL,
        [Amount]      INT           NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    Database table data:

    Result:

    Hope this can help you.

    Best regards,

    Sean

    Wednesday, March 4, 2020 5:26 AM