none
display rows in columns in SQL

    Question

  • hello,


    i have a table as below


    TABLE [dbo].[item_sale]
    ==============
    [id] [int] IDENTITY(1,1) NOT NULL,
    [shakhaname] [nvarchar](150) 
    [tarik] [datetime] NULL,
    [kharidarName] [nvarchar](150) 
    [vaarasnaam] [nvarchar](50) 
    [gaamName] [nvarchar](150) 
    [itemcategory] [nvarchar](50) 
    [itemname] [nvarchar](150) 
    [utpadakName] [nvarchar](150) 
    [biyaranvarg] [nvarchar](50) 
    [paaknaam] [nvarchar](150) 
    [paakvistar] [float] NULL,
    [banavatlotno] [nvarchar](50) 
    [vechanvigat] [nvarchar](50) 
    [vechanbhavperkl] [float] NULL,
    [vechanjatho] [float] NULL,
    [kulkimmat] [float] NULL,
    [billno] [varchar](20) 
    [sale_type] [nvarchar](15) 
    [sale_comm] [float] NULL,
    [rahat_amt] [float] NULL,
    [entry_dt] [datetime] NULL,
    [entry_by] [nvarchar](50


    I have to display records in column format based on the entry_dt, BILLNO field in the below mentioned format


    entry_dt Billno  kharidarname  gaamname itemname kulkimmat rahat_amt cashonhand(kulkimmat-rahat_amt)
    ===== ===  ======== ======= ====== ====== ====== ====================


    in the above result format the columns keep adding themself coz there are a maximum of 10 items in each bill 

    Need guidance for generating an SP or a query please

    Im doing this proj in VB.net

    thanks rgds
    hari vaag


    HV

    • Déplacé KJian_ mercredi 29 février 2012 06:37 (From:SQL Server Express)
    mardi 28 février 2012 04:01

Réponses

  • The SP first does not give me a correct total....

    More exactly what is wrong?

    secondly when i run from my VB.net code it gives me an error as described in the link.

    So there are some errors in the VB code you have in that forum. Before I explain where the errors are, I like to make a statement. What you get here is assistance. That is, we give you an outline for a solution. Sometimes that may be a complete script, but this is very important: at no point we relieve you from the chores of thinking. You cannot just take the code as-is, you need make some effort to understand it. OK, so the XML PATH bit is a bit of mumbo jumbo and you can consider it as such. But the rest you need to have some understanding of. If not, you need to tell your manager or client that you don't have the knowledge for the task and they need to find someone else. After all, you presumably get paid to produce this code - we don't get paid to help you. Which is alright by me as long as you try to learn something from the advice we give you.

    OK, so back to the technical bit. In your VB code you have:

            cmd.Parameters.Add("@repeatpart", SqlDbType.NVarChar, 100)
            cmd.Parameters("@repeatpart").Value = ""
            cmd.Parameters.Add("@SQL", SqlDbType.NVarChar, 100)
            cmd.Parameters("@SQL").Value = ""
            cmd.Parameters.Add("@nlmarker", SqlDbType.Char, 6)
            cmd.Parameters("@nlmarker").Value = ""

    But @repeatpart, @SQL and @nlmarker are local parameters in your stored procedure, and not parameters.

    As I understand the thread in the VB forum, you don't get an error message; you simply do not get any rows back. Your post is that forum is also confusing, because you say you have a stored procedure, but you post a batch of SQL code which is not a stored procedures. Which opens the question of how the parameter list looks like.

    In any case, below there is my complete repro script that I have used to test my solutions. It creates the table (where I had to drop the IDENTITY property to get the INSERT to work), it includes your INSERT statements (where I had to drop the last value 'sale') to get them to work. Then comes my query batch, and the end I drop the table (so that I can easily run the script again).

    I suggest that you compose a complete repro of the same kind which demonstrates your problems. That is, instead of a batch, you should have your stored procedure and the EXEC statement to call it. Test the script in tempdb (so that you don't mess up your real table) before you post it.

    CREATE TABLE [dbo].[item_sale](
       [id] [int]  NOT NULL,
       [shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [tarik] [datetime] NULL,
       [kharidarname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [gaamname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [ItemName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [utpadaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [paakvistar] [float] NULL,
       [banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [vechanbhavperkl] [float] NULL,
       [vechanjatho] [float] NULL,
       [kulkimmat] [float] NULL,
       [BillNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [sale_comm] [float] NULL,
       [rahat_amt] [float] NULL,
       [entry_dt] [datetime] NULL,
       [entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [Total_Amt] [float] NULL,
     CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED
    (
       [id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    go
    insert into item_sale values('34','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 1:04:00 PM','hari','3000');
    insert into item_sale values('35','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','2400');
    insert into item_sale values('36','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','23000');
    insert into item_sale values('37','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf ','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','27000');
    insert into item_sale values('38','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf ','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','1000');
    insert into item_sale values('39','4rad','3/23/2012 11:05:35 AM','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko ','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05:00 AM','hari','14582.5');
    insert into item_sale values('41','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2300');
    insert into item_sale values('42','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2700');
    go
    SELECT  * FROM item_sale ORDER BY BillNo, id
    go
    DECLARE @repeatpart nvarchar(MAX),
            @SQL nvarchar(MAX),
            @nlmarker char(6)

    SELECT @nlmarker  = '<CRNL>'

    ; WITH numbered AS (
       SELECT ItemName,
              rn = row_number() OVER(ORDER BY ItemName)
       FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
    )
    SELECT @repeatpart =
       (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
        FROM   numbered
        FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    -- Replace @nlmarker with comma, CR-LF and an indent.
    SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

    -- Compose the final SQL.
    SELECT @SQL =
    'SELECT BillNo, kharidarname, gaamname,
    ' + @repeatpart + ' SUM(Total_Amt) AS Total_Amt_BillNo
    FROM    item_sale
    GROUP   BY BillNo, kharidarname, gaamname WITH ROLLUP
    HAVING grouping(BillNo) = 1 AND grouping(kharidarname) = 1 AND grouping(gaamname) = 1 OR
           grouping(BillNo) = 0 AND grouping(kharidarname) = 0 AND grouping(gaamname) = 0
    '

    print @SQL -- test
    execute(@SQL) 
    go

    drop table item_sale


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    samedi 31 mars 2012 09:27
  • In this case, how about:

    -- Compose the final SQL.
    SELECT @SQL = 
    'SELECT BillNo, kharidarname, gaamname, 
    ' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
    FROM    item_sale
    WHERE [SaleDate] between @StartDate and @EndDate and
    CategoryID = @CategoryID
    
    GROUP   BY BillNo, kharidarname, gaamname
    UNION ALL
    SELECT ''Total'', NULL as kharidarname, NULL as gaamname, 
    ' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
    FROM    item_sale
    WHERE [SaleDate] between @StartDate and @EndDate and
    CategoryID = @CategoryID'
    
    print @SQL -- test
    execute sp_executeSQL @SQL, N'@StartDate datetime, @EndDate datetime, @CategoryID int', @StartDate, @EndDate, @CategoryID
    This will produce just 1 extra total line. Note also the minor change I made in the previous message of a solution. (Replaced MAX with SUM for total_amount and qty fields).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    mercredi 28 mars 2012 03:34
    Modérateur

Toutes les réponses

  • hello,
    i have a table as below
    TABLE [dbo].[item_sale]
    ==============
    [id] [int] IDENTITY(1,1) NOT NULL,
    [shakhaname] [nvarchar](150) 
    [tarik] [datetime] NULL,
    [kharidarName] [nvarchar](150) 
    [vaarasnaam] [nvarchar](50) 
    [gaamName] [nvarchar](150) 
    [itemcategory] [nvarchar](50) 
    [itemname] [nvarchar](150) 
    [utpadakName] [nvarchar](150) 
    [biyaranvarg] [nvarchar](50) 
    [paaknaam] [nvarchar](150) 
    [paakvistar] [float] NULL,
    [banavatlotno] [nvarchar](50) 
    [vechanvigat] [nvarchar](50) 
    [vechanbhavperkl] [float] NULL,
    [vechanjatho] [float] NULL,
    [kulkimmat] [float] NULL,
    [billno] [varchar](20) 
    [sale_type] [nvarchar](15) 
    [sale_comm] [float] NULL,
    [rahat_amt] [float] NULL,
    [entry_dt] [datetime] NULL,
    [entry_by] [nvarchar](50


    I have to display records in column format based on the entry_dt, BILLNO field in the below mentioned format


    entry_dt Billno kharidarname gaamname itemname kulkimmat rahat_amt cashonhand(kulkimmat-rahat_amt)
    ===== === ======== ======= ====== ====== ====== ====================


    in the above result format the columns keep adding themself coz there are a maximum of 10 items in each bill 
    Need guidance for generating an SP or a query please
    Im doing this proj in VB.net


    thanks rgds
    hari vaag

    HV

    • Fractionné Naomi NModerator mardi 28 février 2012 04:32 New question - old thread
    • Fusionné KJian_ mercredi 29 février 2012 06:35 duplicate
    mardi 28 février 2012 04:11
  • The idea will be the same as the thread you linked to:

    ;with cte as (select BillNo, entry_dt, ItemName, 
    row_number() over (partition by BillNo, entry_dt order by ItemName) as Rn from YourTable)
    
    select * from cte PIVOT (max(ItemName) 
    FOR Rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) pvt
    The above will create a table with fixed number of items per record (10). If you want to generate dynamic structure (using current max number per bill), then you will need to do it dynamically, but it still will be the same idea.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    mardi 28 février 2012 04:39
    Modérateur
  • hi,

    it's not clear to me what you are trying "to rotate"... anyway, you can perhaps have a look at the following code to see if it fits your needs..

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
    	Id int NOT NULL IDENTITY,
    	BillNo int NOT NULL,
    	Value decimal(18,4) NOT NULL
    	);
    GO
    INSERT INTO dbo.t 
    	VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);
    
    INSERT INTO dbo.t 
    	VALUES (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7);
    GO
    WITH cte AS (
    	SELECT *, ROW_NUMBER() OVER (PARTITION BY BillNo ORDER BY Id) AS [r]
    		FROM dbo.t
    		WHERE 1 = 1 -- your filter conditions
    	)
    SELECT c.BillNo,
    	SUM(CASE WHEN r = 1 THEN Value ELSE NULL END) AS [col1],
    	SUM(CASE WHEN r = 2 THEN Value ELSE NULL END) AS [col2],
    	SUM(CASE WHEN r = 3 THEN Value ELSE NULL END) AS [col3],
    	SUM(CASE WHEN r = 4 THEN Value ELSE NULL END) AS [col4],
    	SUM(CASE WHEN r = 5 THEN Value ELSE NULL END) AS [col5],
    	SUM(CASE WHEN r = 6 THEN Value ELSE NULL END) AS [col6],
    	SUM(CASE WHEN r = 7 THEN Value ELSE NULL END) AS [col7],
    	SUM(CASE WHEN r = 8 THEN Value ELSE NULL END) AS [col8],
    	SUM(CASE WHEN r = 9 THEN Value ELSE NULL END) AS [col9],
    	SUM(CASE WHEN r = 10 THEN Value ELSE NULL END) AS [col10]
    	FROM cte c
    	GROUP BY BillNo;
    GO
    DROP TABLE dbo.t;
    --<------------
    BillNo      col1     col2    col3    col4    col5    col6    col7    col8    col9    col10
    ----------- -------- ------- ------- ------- ------- ------- ------- ------- ------- --------
    1           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  8.0000  9.0000  10.0000
    2           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  NULL    NULL    NULL
    Warning: Null value is eliminated by an aggregate or other SET operation.
    

    first I "numbered" the rows of each BillNo with the ROW_NUMBER function, then I aggregated them with the GROUP BY clause manually rotating the rows to columns based on the ROW_NUMBER value, using the CASE WHEN sintax to filter the desired row/column... but it's completely manual.. if your design will in the future allow, say, 11 rows/column you have to modify the projection... or you completely dinamically build it on the fly, similarly to the following,

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
    	Id int NOT NULL IDENTITY,
    	BillNo int NOT NULL,
    	Value decimal(18,4) NOT NULL
    	);
    GO
    INSERT INTO dbo.t 
    	VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);
    
    INSERT INTO dbo.t 
    	VALUES (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7);
    GO
    DECLARE @maxRow int;
    WITH cte AS (
    	SELECT ROW_NUMBER() OVER (PARTITION BY BillNo ORDER BY Id) AS [r]
    		FROM dbo.t
    		WHERE 1 = 1 -- your filter conditions
    	)
    	SELECT @maxRow = MAX(r)
    		FROM cte;
    
    DECLARE @nro int = 1;
    DECLARE @cmd varchar(1000) = '';
    WHILE @nro <= @maxRow BEGIN
    	SET @cmd = @cmd + CASE WHEN LEN(@cmd) <> 0 THEN ',' + CHAR(13) + CHAR(10) ELSE '' END
    		+ 'SUM(CASE WHEN r = ' + CONVERT(varchar, @nro) + ' THEN Value ELSE NULL END) AS [col' + CONVERT(varchar, @nro) + ']';
    	SET @nro +=1;
    END;
    SET @cmd = 'WITH cte AS (
    	SELECT *, ROW_NUMBER() OVER (PARTITION BY BillNo ORDER BY Id) AS [r]
    		FROM dbo.t
    		WHERE 1 = 1 -- your filter conditions
    	)
    SELECT c.BillNo,' + CHAR(13) + CHAR(10)
    	+ @cmd + CHAR(13) + CHAR(10)
    	+ '		FROM cte c'  + CHAR(13) + CHAR(10)
    	+ '		GROUP BY BillNo';
    
    
    EXEC (@cmd);
    GO
    DROP TABLE dbo.t;
    --<----------
    BillNo      col1     col2    col3    col4    col5    col6    col7    col8    col9    col10
    ----------- -------- ------- ------- ------- ------- ------- ------- ------- ------- --------
    1           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  8.0000  9.0000  10.0000
    2           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  NULL    NULL    NULL
    Warning: Null value is eliminated by an aggregate or other SET operation.

    where you first individuate the max of the rotation and then dinamically build the statement to rotate the results... but such scenarios really are "bad" :)

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    mercredi 29 février 2012 00:55
  • hello thanks for you suggestion and guidance, but the client needs it in this way, so i have to give it in print and display....


    ill explain it again. my table has date, billno, itemname, itemqty, itemamt, totalamt. 
    each bill can have max 10 items. now when the client wants to view the sale for a particular date, he should get the result in column format with fields(itemname,itemqty, itemamt, totalamt) getting displayed column wise at the end of the resultset for that particular date, there should be a total at the bottom for each item


    hope the above is clear..


    rgds

    HV

    mercredi 29 février 2012 04:59
  • In this case you're talking about case based pivot, e.g.

    ;with cte as (select date, BillNo, ItemName, ItemQty, TotalAmt, row_number() over (partition by Date, BillNo order by ItemName) as Rn from BillInfo) select date, BillNo, max(case when Rn = 1 then ItemName end) as Item1,

    max(case when Rn =1 then ItemQty end) as Qty1,

    max(case when Rn = 1 then TotalAmt end) as TotalAmt1, max(case when Rn = 2 then ItemName end) as Item2,

    max(case when Rn =2 then ItemQty end) as Qty2,

    max(case when Rn = 2 then TotalAmt end) as TotalAmt2, etc. from cte GROUP BY date, BillNo


    I suggest to add totals per item in the reporting software.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    mercredi 29 février 2012 16:08
    Modérateur
  • This type of report is best done in a report wrting tool such as SSRS

    Dave Frommer - BI Architect - Independent

    vendredi 2 mars 2012 12:28
  • thanks

    can you guide me as to where do i start for SSRS please

    rgds


    HV

    vendredi 2 mars 2012 17:18
  • Take a look at this blog post

    Report Builder 3.0 - Table or Matrix Wizard


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    vendredi 2 mars 2012 19:05
    Modérateur
  • thanks Naomi N

    i tried using the below 

    ;with cte as (select entry_dt, BillNo, ItemName, vechanjatho, Total_Amt, row_number() over (partition by entry_dt, BillNo order by ItemName) as Rn
    from item_sale)
    select entry_dt, BillNo,
    max(case when Rn = 1 then ItemName end) as Item1, 
    max(case when Rn =1 then vechanjatho end) as Qty1,
    max(case when Rn = 1 then Total_Amt end) as TotalAmt1,
    max(case when Rn = 2 then ItemName end) as Item2, 
    max(case when Rn =2 then vechanjatho end) as Qty2, 
    max(case when Rn = 2 then Total_Amt end) as TotalAmt2
    from cte
    GROUP BY entry_dt, BillNo


    and the output i get is different lines

    2012-03-01 09:50:00.000	1000	dva A 	95	950	NULL	NULL	NULL
    2012-03-01 09:51:00.000	1000	dva b	100	2500	NULL	NULL	NULL
    2012-03-01 09:50:00.000	1001	dva A 	95	900	NULL	NULL	NULL
    2012-03-01 09:51:00.000	1001	dva B 	100	2550	NULL	NULL	NULL

    what i need is the bill no 1000 should display all records in one line (a max is upto 10 items in each bill)

    can you kindly guide me with this please

    rgds


    HV

    samedi 3 mars 2012 05:27
  • Looks like we don't want to include date field. Remove date field, e.g.

    ;with cte as (select BillNo, ItemName, ItemQty, TotalAmt, row_number() over (partition by BillNo order by ItemName, date) as Rn
    
    from BillInfo)
    
    select BillNo,
    
    max(case when Rn = 1 then ItemName end) as Item1, 
    max(case when Rn =1 then ItemQty end) as Qty1,
     max(case when Rn = 1 then TotalAmt end) as TotalAmt1,
    
    max(case when Rn = 2 then ItemName end) as Item2, 
    max(case when Rn =2 then ItemQty end) as Qty2, 
    max(case when Rn = 2 then TotalAmt end) as TotalAmt2,
    
    etc.
    
    from cte
    
    GROUP BY BillNo


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    dimanche 4 mars 2012 02:27
    Modérateur
  • thanks 

    ive tweaked the code for my needs and ive got the desired output. thanks a ton for the same....

    below is the code. only 2 things which are left in this

    1. my columns are max 10, but if they are below 10, then how do i display only those columns which are not null, which means that RN in the code can run a max up to 10, but if the rows are only 3 then it should only show 3 columns(means RN).

    2. how do i add a total row wise and column wise please

    ;with cte as (select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() over (partition by BillNo order by ItemName, entry_dt) as Rn
    
    from item_sale)
    
    select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname,  max(gaamname) as gaamname,
    
    max(case when Rn = 1 then ItemName end) as Item1, 
    max(case when Rn =1 then vechanjatho end) as Qty1,
    max(case when Rn = 1 then Total_Amt end) as TotalAmt1,
    max(case when Rn = 2 then ItemName end) as Item2, 
    max(case when Rn =2 then vechanjatho end) as Qty2, 
    max(case when Rn = 2 then Total_Amt end) as TotalAmt2
    from cte
    GROUP BY BillNo

    rgds


    HV

    dimanche 4 mars 2012 03:55
  • If you want the number of columns to be dynamic, you will use the exactly same idea, but dynamically, e.g.

    select entry_dt,BillNo,kharidarname, gaamname, ItemName, vechanjatho, Total_Amt, row_number()

    over (partition by BillNo order by ItemName, entry_dt) as Rn into #TempResults from item_sale declare @SQL nvarchar(max), @Rows smallint, @Loop smallint select @Rows = max(Rn) from #TempResults select @Loop = 1, @SQL = '' while @Loop <=@Rows BEGIN SET @SQL = @SQL + ' ,max(case when Rn = ' + cast(@Loop as varchar(5)) + ' then ItemName END as ItemName' + cast(@Loop as varchar(5)) + ',max(case when Rn =' + cast(@Loop as varchar(5)) + ' then vechanjatho end) as Qty' + + cast(@Loop as varchar(5)) + ',max(case when Rn = '+ cast(@Loop as varchar(5)) + ' then Total_Amt end) as TotalAmt'+ cast(@Loop as varchar(5)) SET @Loop = @Loop + 1 END SET @SQL = 'select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname,

    max(gaamname) as gaamname' + @SQL + ' GROUP BY BillNo' print @SQL -- test execute(@SQL)

    Once you get it working, you can also add totals to this query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    dimanche 4 mars 2012 04:14
    Modérateur
  • thanks again, but below is the error i get

    Msg 102, Level 15, State 1, Line 15
    Incorrect syntax near '@SQL'.
    Msg 156, Level 15, State 1, Line 20
    Incorrect syntax near the keyword 'SET'.
    


    HV

    dimanche 4 mars 2012 16:45
  • There's a missing SELECT:

    SELECT @SQL = @SQL + '
    

    dimanche 4 mars 2012 16:57
  • thanks

    i get a new error, i know what the problem is but dont know how to solve it.

    select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname, max(gaamname) as gaamname max(case when Rn = 1 then ItemName END) as ItemName1,max(case when Rn =1 then vechanjatho end) as Qty1,max(case when Rn = 1 then Total_Amt end) as TotalAmt1max(case when Rn = 2 then ItemName END) as ItemName2,max(case when Rn =2 then vechanjatho end) as Qty2,max(case when Rn = 2 then Total_Amt end) as TotalAmt2max(case when Rn = 3 then ItemName END) as ItemName3,max(case when Rn =3 then vechanjatho end) as Qty3,max(case when Rn = 3 then Total_Amt end) as TotalAmt3max(case when Rn = 4 then ItemName END) as ItemName4,max(case when Rn =4 then vechanjatho end) as Qty4,max(case when Rn = 4 then Total_Amt end) as TotalAmt4 from item_sale GROUP BY BillNo
    
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'max'.

    error is that after every totalamt in the select case there is a (,) missing, but how do i add it, coz when i add it also adds a comma at the end of the SQL statement.

    kindly help

    rgds


    HV

    dimanche 4 mars 2012 17:14
  • C'mon, is it really that hard to figure out there's a missing comma? One reason might be the lack of proper code formatting though.

    I'm not sure if the task you're faced with is compliant with the skills available...

    dimanche 4 mars 2012 17:19
  • my dear friend, i know the problem, and i know the solution that i have to check that the loop and rows values if they are equal it should not put the (,), but im not aware of the syntax, thats the help im asking for .... rgds

    HV

    dimanche 4 mars 2012 17:26
  • hello thanks a ton for all your help, ive tweaked your code and made it work exactly the way i want results. only thing what i need now is row wise and column wise total please.... below is the code

    drop table #tempresults
    select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() 
    over (partition by BillNo order by ItemName, entry_dt) as Rn
    into #TempResults
    from item_sale
    declare @SQL nvarchar(max), @Rows smallint, @Loop smallint;
    select @Rows = max(Rn) from #TempResults;
    print @rows
    select @Loop = 1, @SQL = ' ';
    while @Loop <=@Rows
    BEGIN
    select @SQL = @SQL + 'max(case when Rn = ' + cast(@Loop as varchar(5)) + ' then ItemName END) as ItemName' + cast(@Loop as varchar(5)) + 
    ',max(case when Rn =' + cast(@Loop as varchar(5)) +  ' then vechanjatho end) as Qty' + cast(@Loop as varchar(5)) + 
    ',max(case when Rn = '+ cast(@Loop as varchar(5)) + ' then Total_Amt end) as TotalAmt'+ cast(@Loop as varchar(5)) 
    if @loop < @Rows
    begin 
    select @sql = @sql +','
    end
    print @loop
     SET @Loop = @Loop + 1;
     END
    SET @SQL = 'select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname, max(gaamname) as gaamname, ' + @SQL + ' from #tempResults GROUP BY BillNo'
    print @SQL -- test
    execute(@SQL)  
    

    my results are as below

    2012-03-01 09:51:00.000	1000	harI	pa3`pura	dva A 	95	950	dva b	100	2500	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-01 09:51:00.000	1001	harI	pa3`pura	dva A 	95	900	dva B 	100	2550	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-02 09:50:00.000	1002	harI	pa3`pura	dva A 	10	1500	dva b	20	2000	dva C	30	2500	dva D	40	3000

    kindly help me get the totals please

    rgds


    HV

    dimanche 4 mars 2012 17:53
  • Can you post your input (as DDL + insert statements with data) and desired output? BTW, what are your own thoughts of how to get the desired result?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    dimanche 4 mars 2012 20:27
    Modérateur
  • hello,

    i did not get your question. can you kindly explain please...

    rgs


    HV

    lundi 5 mars 2012 16:48
  • Take a look at how this question is asked

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/de09496b-701d-4609-ae31-2adc084c18ed/ 

    I want you to ask your question similar way - e.g. provide structure for your table and data as insert statements and then what kind of output you want based on input. So far I wrote all the queries from the top of my head - but having a working sample will also help to create a working solution which you would not need to tweak.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    lundi 5 mars 2012 17:03
    Modérateur
  • hello,

    your help has so far solved my query, but now i need the output itemwise so that i can total it at the end of the page.

    my desired output format is below

    2012-03-01 09:51:00.000	1000	harI	pa3`pura	dva A 	95	950	dva b	100	2500	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-01 09:51:00.000	1001	harI	pa3`pura	dva A 	95	900	dva B 	100	2550	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-02 09:50:00.000	1002	harI	pa3`pura	dva A 	10	1500	dva b	20	2000	dva C	30	2500	dva D	40	3000

    my table structure as below

    CREATE TABLE [dbo].[item_sale](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[tarik] [datetime] NULL,
    	[kharidarName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[gaamName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[itemname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[utpadakName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[paakvistar] [float] NULL,
    	[banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[vechanbhavperkl] [float] NULL,
    	[vechanjatho] [float] NULL,
    	[kulkimmat] [float] NULL,
    	[billno] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[sale_comm] [float] NULL,
    	[rahat_amt] [float] NULL,
    	[entry_dt] [datetime] NULL,
    	[entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[total_amt] [float] NULL,
     CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    


    and insert statements as below

    insert into item_sale values('4rad','3/7/2012 13:21','dcdcd','','dcdc','j>taunaxk dva','dva 1','taru~a','','','0','dcdcd c','10','100','100','10000','1000','cashmemo','0','0','3/7/2012 13:22','hari','10000')
    insert into item_sale values('4rad','3/12/2012 16:52','manv','','paT~apura','j>taunaxk dva','dva 1','taru~a','','','0','jkn nknk 9879878','12','25','100','2500','1001','cashmemo','0','0','3/12/2012 16:53','','2500')
    insert into item_sale values('4rad','3/12/2012 16:55','p/vI~a','','moDera','j>taunaxk dva','dva 1','taru~a','','','0','cdv dfvfd vfd','0','25','10','250','1002','cashmemo','0','0','3/12/2012 16:56','','250')
    insert into item_sale values('4rad','3/12/2012 16:55','p/vI~a','','moDera','j>taunaxk dva','dva 1','taru~a','','','0','cdv dfvfd vfd','12','25','12','300','1002','cashmemo','0','0','3/12/2012 16:56','','300')
    insert into item_sale values('4rad','3/12/2012 18:16','fvfv','fvf vf','dvdvdfvdfv fdvfd','bIyar~a','dva n> 1','','','','0','fvfvff vfvf','150','20','100','2000','1500','cashmemo_1','0','0','3/12/2012 18:16','','2000')
    insert into item_sale values('4rad','3/12/2012 18:32','dcdscdsc','','sdcdscdsc','bIyar~a','dva n> 1','','','','0','','','20','100','2000','1','rahat','40','500','3/12/2012 18:32','','1540')
    insert into item_sale values('4rad','3/12/2012 18:36','fvfvfdvf','','fvfvfv','bIyar~a','dva n> 1','','','','0','','','20','100','2000','2','rahat','50','500','3/12/2012 18:36','','1550')
    

    hope my question is clear for you to help me.... I have to display the results sorted by itemwise and total it row wise...

    rgds


    HV

    mercredi 14 mars 2012 03:27
  • hello,

    can anyone please help me on this 

    rgds


    HV

    jeudi 15 mars 2012 13:47
  • hello,

    i have given the details below, can you please help me with the final SP pls

    rgds


    HV

    lundi 19 mars 2012 17:34
  • What is the result you're currently getting and what is the result you want to get? Did you try adding GROUPING SETS to the original query to introduce vertical total?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    lundi 19 mars 2012 17:41
    Modérateur
  • hello

    the results that i need are as below

    2012-03-01 09:51:00.000	1000	harI	pa3`pura	dva A 	95	950	dva b	100	2500	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-01 09:51:00.000	1001	harI	pa3`pura	dva A 	95	900	dva B 	100	2550	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-02 09:50:00.000	1002	harI	pa3`pura	dva A 	10	1500	dva b	20	2000	dva C	30	2500	dva D	40	3000

    which means that the grouping has to be done on Billno first and then itemname, only then i will be able to give the total verticaly. but  the results which im getting currently is in the serial the user is buying the product in ... like in the above result, the user may buy "dva B" first and then the second item as "dva A" so i need to group by billno, itemname

    lemme know if im not clear pls....

    rgds


    HV

    lundi 19 mars 2012 17:48
  • Sorry, I don't understand you. Show your current output, what is wrong with it and what output do you want. I don't understand the above output and I don't see any totaling. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    lundi 19 mars 2012 18:11
    Modérateur
  • Hello 

    Below is the table structure

    CREATE TABLE [dbo].[item_sale](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[tarik] [datetime] NULL,
    	[kharidarName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[gaamName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[itemname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[utpadakName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[paakvistar] [float] NULL,
    	[banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[vechanbhavperkl] [float] NULL,
    	[vechanjatho] [float] NULL,
    	[kulkimmat] [float] NULL,
    	[billno] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[sale_comm] [float] NULL,
    	[rahat_amt] [float] NULL,
    	[entry_dt] [datetime] NULL,
    	[entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[total_amt] [float] NULL,
     CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]


    Below is the SP

    drop table #tempresults
    declare @SQL nvarchar(max), 
    @Rows smallint, 
    @Loop smallint
    
    select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() 
    over (partition by BillNo order by ItemName, entry_dt) as Rn
    into #TempResults
    from item_sale
    select @Rows = max(Rn) from #TempResults;
    print @rows
    select @Loop = 1, @SQL = ' ';
    while @Loop <=@Rows
    BEGIN
    select @SQL = @SQL + 'max(case when Rn = ' + cast(@Loop as varchar(5)) + ' then ItemName END) as ItemName' + cast(@Loop as varchar(5)) + 
    ',max(case when Rn =' + cast(@Loop as varchar(5)) +  ' then vechanjatho end) as Qty' + cast(@Loop as varchar(5)) + 
    ',max(case when Rn = '+ cast(@Loop as varchar(5)) + ' then Total_Amt end) as TotalAmt'+ cast(@Loop as varchar(5)) 
    if @loop < @Rows
    begin 
    select @sql = @sql +','
    end
    print @loop
     SET @Loop = @Loop + 1;
     END
    SET @SQL = 'select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname, max(gaamname) as gaamname, ' + @SQL + ' from #tempResults GROUP BY BillNo,ItemName'
    
    print @SQL -- test
    execute(@SQL)  
    

    below is the results which im getting but that is NOT CORRECT

    2012-03-20 13:04:00.000	1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-22 17:14:00.000	1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	NULL	NULL	NULL	NULL	NULL	NULL
    2012-03-22 17:14:00.000	1001	dcdcd 	dcdc d	NULL	NULL	NULL	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL
    2012-03-20 13:05:00.000	1000	hrI	paT~apura	NULL	NULL	NULL	rIjNT bayr 100 g/am	20	2400	NULL	NULL	NULL
    2012-03-20 13:05:00.000	1000	hrI	paT~apura	NULL	NULL	NULL	NULL	NULL	NULL	rIjNT bayr 250 g/am	100	23000
    2012-03-23 11:05:00.000	1002	dcdc	dth bb	yuirya {fko 	50	14582.5	NULL	NULL	NULL	NULL	NULL	NULL

    the correct result what i want is below

    Entry_DT	Billno	kharidaarname	gaamname	itemname1	qty1	total1	itemname2	qty2	total2	itemname3	qty3	total3	itemname4	qty4	total4	itemname5	qty5	total4
    3/20/2012	1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	NULL	NULL	NULL
    3/22/2012	1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	NULL	NULL	NULL	NULL	NULL	NULL	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL
    3/23/2012	1002	dcdc	dth bb	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL				yuirya {fko 	50	14582.5
    

    There is no limit to the itemnames so it can go to itemname1.... itemname...nth value

    i need the items to be listed in such a way that i can get a total at the end of the report vertically. this is possible only when i can group the itemnames with the same name.


    rgds


    HV

    samedi 24 mars 2012 09:00
  • It would certainly help if you could post INSERT statemetns with sample data and the result you want from that data. It does not have to be that many, but some 20 rows.

    Also, please bear in mind that since the column names is partly in your local language, they are a bit of nonsense to some of us.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    samedi 24 mars 2012 10:51
  • thankx

    below are insert statements

    insert into item_sale values('34','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 1:04:00 PM','hari','3000','sale');
    insert into item_sale values('35','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','2400','sale');
    insert into item_sale values('36','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','23000','sale');
    insert into item_sale values('37','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf ','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','27000','sale');
    insert into item_sale values('38','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf ','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','1000','sale');
    insert into item_sale values('39','4rad','3/23/2012 11:05:35 AM','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko ','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05:00 AM','hari','14582.5','sale');
    insert into item_sale values('41','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2300','sale');
    insert into item_sale values('42','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2700','sale');

    and the output that i want is as below

    Entry_DT	Billno	kharidaarname	gaamname	itemname1	qty1	total1	itemname2	qty2	total2	itemname3	qty3	total3	itemname4	qty4	total4	itemname5	qty5	total4
    3/20/2012	1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	NULL	NULL	NULL
    3/22/2012	1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	NULL	NULL	NULL	NULL	NULL	NULL	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL
    3/23/2012	1002	dcdc	dth bb	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL				yuirya {fko 	50	14582.5

    thanks and regards


    HV

    samedi 24 mars 2012 13:46
  • Thanks for the INSERT statements! I had some problem to get them to work, but I stripped of the last column from the VALUES clause and I was able to insert the data. Next time you post sample data, please test that it works with the CREATE TABLE script you have posted.

    So here is a query and a challenge. The challenge is for me to explain what the beast actually does...

    Anyway, the result is not exactly as you posted it, but it is my interpretation of your narrative. You want three columns for each indivdual item: Name, Qty and Amt. Is this correctly understood?

    Here is the query:

    DECLARE @repeatpart nvarchar(MAX),
            @SQL nvarchar(MAX),
            @nlmarker char(6) = '<CRNL>'

    ; WITH numbered AS (
       SELECT ItemName,
              rn = row_number() OVER(ORDER BY ItemName)
       FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
    )
    SELECT @repeatpart =
       (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
               ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
               ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
        FROM   numbered
        FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    -- Strip last @nlmarker.
    SELECT @repeatpart = substring(@repeatpart, 1, len(@repeatpart) - len(@nlmarker))

    -- Replace @nlmarker with comma, CR-LF and an indent.
    SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

    -- Compose the final SQL.
    SELECT @SQL =
    'SELECT BillNo, kharidarname, gaamname,
    ' + @repeatpart + '
    FROM    item_sale
    GROUP   BY BillNo, kharidarname, gaamname'

    print @SQL -- test
    execute(@SQL) 

    To make it a little easier, I compose the SQL in two steps. I first compose the repeated part, the one with all the MAX. The initial CTE is simple: it gets all distinct item names and number them.

    I then use FOR XML PATH('') to concatenate all rows into a single string. Instead of using a loop, I can do it in one statement. It's certainly obscure, but it is the standard method to composed a concatenated list from a table source. But since it's XML, characters like < are tokenised, so I need to extract the actual charcter value, and that's the .value() thing at the end.

    The @nlmarker is added once too many, so I strip the last one. Then I replace it with the characters I want in that place. (I don't think XML handles embedded CR-LF too well.

    Then I composed the final query, print it and execute.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    samedi 24 mars 2012 22:34
  • hi 

    thanks for your reply....

    I get error as below on the 3rd line "<CRNL>"

    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 2, Line 12
    Must declare the scalar variable "@nlmarker".
    Msg 137, Level 15, State 2, Line 21
    Must declare the scalar variable "@nlmarker".
    Msg 137, Level 15, State 2, Line 24
    Must declare the scalar variable "@nlmarker".

    when i edit the 3rd line to "@nlmarker char(6)" i dont get any result....

    can you kindly help on this pls

    rgds


    HV

    dimanche 25 mars 2012 16:49
  • So you are running on SQL 2005. Excuse me for being a bit brave (well lazy),
    and assuming that you are on SQL 2008.

    The fix is simple. Change

    DECLARE @repeatpart nvarchar(MAX),
            @SQL nvarchar(MAX),
            @nlmarker char(6) = '<CRNL>'

    to

    DECLARE @repeatpart nvarchar(MAX),
            @SQL nvarchar(MAX),
            @nlmarker char(6)

    SELECT @nlmarker = '<CRNL>'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marqué comme réponse Hari Vaag lundi 26 mars 2012 13:02
    • Non marqué comme réponse Hari Vaag lundi 26 mars 2012 13:02
    dimanche 25 mars 2012 19:20
  • A million thanks for the solution...

    Just need 2 more things if you can help me out with please..

    1. Row wise total of the amount at the end of the report.

    2. i need to pass 3 parameters --- from date, to date, category name

    kindly help me with the same in your SP please

    rgds

    • Modifié Hari Vaag lundi 26 mars 2012 13:09 Forgot the point
    lundi 26 mars 2012 13:01
  • Row wise total is a simple sum of TotalAmount. What seems to be the problem of passing the parameters?

    DECLARE @repeatpart nvarchar(MAX), 
            @SQL nvarchar(MAX),
            @nlmarker char(6) 
    
    declare @CategoryID int -- will get category id based on passed Category Name
    select @CategoryID = CategoryID from dbo.Categories where
    CategoryName = @Category
    
    SET @nlmarker= '<CRNL>'
    
    ; WITH numbered AS (
       SELECT ItemName, 
              rn = row_number() OVER(ORDER BY ItemName)
       FROM   (SELECT DISTINCT ItemName FROM item_sale
    
    where [SaleDate] between @StartDate and @EndDate and
    CategoryID = @CategoryID
    
    ) AS d
    )
    SELECT @repeatpart = 
       (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') + 
                    ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') + 
                    ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') + 
                    ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
        FROM   numbered
        FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
    
    -- Strip last @nlmarker.
    SELECT @repeatpart = substring(@repeatpart, 1, len(@repeatpart) - len(@nlmarker))
    
    -- Replace @nlmarker with comma, CR-LF and an indent.
    SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))
    
    -- Compose the final SQL.
    SELECT @SQL = 
    'SELECT BillNo, kharidarname, gaamname, 
    ' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
    FROM    item_sale
    WHERE [SaleDate] between @StartDate and @EndDate and
    CategoryID = @CategoryID
    
    GROUP   BY BillNo, kharidarname, gaamname'
    
    print @SQL -- test
    execute sp_executeSQL @SQL, N'@StartDate datetime, @EndDate datetime, @CategoryID int', @StartDate, @EndDate, @CategoryID

    I posted this code without consulting with your actual structure and field names, so pick up correct field names for CategoryID and SaleDate.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    lundi 26 mars 2012 13:25
    Modérateur
  • -- Strip last @nlmarker.
    SELECT @repeatpart = substring(@repeatpart, 1, len(@repeatpart) -

    len(@nlmarker))


    -- Replace @nlmarker with comma, CR-LF and an indent.
    SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) +

    char(10) + space(7))


    -- Compose the final SQL.
    SELECT @SQL =
    'SELECT BillNo, kharidarname, gaamname,
    ' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]

    A small simplification: in this case we can keep the last @nlmarker so
    this be changed to:

    -- Replace @nlmarker with comma, CR-LF and an indent.
     SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) +
    char(10) + space(7))

    -- Compose the final SQL.
    SELECT @SQL =
      'SELECT BillNo, kharidarname, gaamname,
      ' + @repeatpart + ' SUM(Total_Amt) as [Total Amount]

    Note that I removed the comma on this line.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    lundi 26 mars 2012 21:15
  • Thanks for your solution,

    My Mistake i probably kept the question in a wrong way....

    Along with the rowwise total I need columnwise total for amount also please

    can you help me with this pls....

    rgds


    HV

    mardi 27 mars 2012 03:21
  • My Mistake i probably kept the question in a wrong way....

    Yes, if you provide table defintion, insert statements and sample data, it so much easier. Not the least, because there is less guessing to do.

    Along with the rowwise total I need columnwise total for amount also please

    I was just waiting for that one. :-) If you are on SQL 2008, the GROUPING SETS feature could come to use. However, I am at work now, so I can't try it. Then again, if you are presenting the data in some grid, doesn't the grid have a sum capability?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    mardi 27 mars 2012 07:26
  • I also suggest to use Grouping Sets. Recently I found a very good blog by Jacob Sebastian explaining it. 

    http://beyondrelational.com/modules/2/blogs/28/posts/10523/t-sql-tuesday-016-summarizing-data-using-grouping-sets.aspx 

    See, if you can figure out a whole solution now based on what we already posted and post it here.

    This will work in SQL 2008 and up.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    mardi 27 mars 2012 13:22
    Modérateur
  • hi,

    thanks, but m on SQL 2005.

    rgds


    HV

    mercredi 28 mars 2012 03:17
  • In this case, how about:

    -- Compose the final SQL.
    SELECT @SQL = 
    'SELECT BillNo, kharidarname, gaamname, 
    ' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
    FROM    item_sale
    WHERE [SaleDate] between @StartDate and @EndDate and
    CategoryID = @CategoryID
    
    GROUP   BY BillNo, kharidarname, gaamname
    UNION ALL
    SELECT ''Total'', NULL as kharidarname, NULL as gaamname, 
    ' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
    FROM    item_sale
    WHERE [SaleDate] between @StartDate and @EndDate and
    CategoryID = @CategoryID'
    
    print @SQL -- test
    execute sp_executeSQL @SQL, N'@StartDate datetime, @EndDate datetime, @CategoryID int', @StartDate, @EndDate, @CategoryID
    This will produce just 1 extra total line. Note also the minor change I made in the previous message of a solution. (Replaced MAX with SUM for total_amount and qty fields).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    mercredi 28 mars 2012 03:34
    Modérateur
  • thanks,

    but this does not add at the bottom

    1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	NULL	NULL	NULL	NULL	NULL	NULL	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	28400
    1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	28000
    1003	jtaInwa[	paT~apura	NULL	NULL	NULL	NULL	NULL	NULL	Aoma{T 250 g/am ^aanuka	10	2700	NULL	NULL	NULL	rIjNT bayr 250 g/am	10	2300	NULL	NULL	NULL	5000
    Total	NULL	NULL	^aanuTop 1 lITr	90	27000	^aanuTop 250 g/am	10	1000	Aoma{T 250 g/am ^aanuka	10	2700	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	61400

    Above is the output i get. in the first item, there are 2 items of the same name with amt(3000+27000) this should ideally become 30000, but it still shows 27000. can you kindly help on this pls...

    rgds


    HV

    mercredi 28 mars 2012 03:50
  • What is the exact SQL you used? Did you notice the change I made (changed MAX to SUM)?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    mercredi 28 mars 2012 04:15
    Modérateur

  • You will need something like

    ORDER BY coalesce(BillNo, 999999999)

    at the bottom, to have the sum line to come last.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    mercredi 28 mars 2012 07:33
  • yes i did notice that, im using SQL 2005 which is default with Visual Studio 2008, but somehow i cannot make it.

    Im new to all this that is the reason need help in detail pls

    rgds


    HV

    mercredi 28 mars 2012 11:18
  • yes i did notice that, im using SQL 2005 which is default with Visual Studio 2008, but somehow i cannot make it.

    The more interesting question is what you have in production. For your development environment, you can always download an install SQL 2008 Express. Or for that matter SQL 2012.

    But it's a good idea to use the same version in development as you will use in production.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    mercredi 28 mars 2012 13:22
  • in production also i have the same version sir


    HV

    mercredi 28 mars 2012 16:55
  • Post your very latest SQL and what is still wrong with it if you need more help. Otherwise please mark correct answers and close the thread.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marqué comme réponse Hari Vaag mercredi 28 mars 2012 17:41
    • Non marqué comme réponse Hari Vaag samedi 31 mars 2012 03:10
    mercredi 28 mars 2012 17:33
    Modérateur
  • in production also i have the same version sir

    Good. Then you should keep development to SQL 2005 as well.

    Here is a version of my original query which has sums both per colunm and row without using UNION (but it may give you more sums than you want). The HAVING clause is somewhat klunky, but it works.

    DECLARE @repeatpart nvarchar(MAX),
            @SQL nvarchar(MAX),
            @nlmarker char(6)

    SELECT @nlmarker  = '<CRNL>'

    ; WITH numbered AS (
       SELECT ItemName,
              rn = row_number() OVER(ORDER BY ItemName)
       FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
    )
    SELECT @repeatpart =
       (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
        FROM   numbered
        FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    -- Replace @nlmarker with comma, CR-LF and an indent.
    SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

    -- Compose the final SQL.
    SELECT @SQL =
    'SELECT BillNo, kharidarname, gaamname,
    ' + @repeatpart + ' SUM(Total_Amt) AS Total_Amt_BillNo
    FROM    item_sale
    GROUP   BY BillNo, kharidarname, gaamname WITH ROLLUP
    HAVING grouping(BillNo) = 1 AND grouping(kharidarname) = 1 AND grouping(gaamname) = 1 OR
           grouping(BillNo) = 0 AND grouping(kharidarname) = 0 AND grouping(gaamname) = 0
    '

    print @SQL -- test
    execute(@SQL) 
    go


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    mercredi 28 mars 2012 21:30
  • hi,

    i tried to tweak your help but could not get the desired output, also the thread was getting too long so i moved it to this with more specific details..

    The SP first does not give me a correct total....

    secondly when i run from my VB.net code it gives me an error as described in the link.

    Ive put in the insert statements of the records which are there in my item_sale table

    insert into item_sale values('4rad','3/20/2012 13:04','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 13:04','hari','3000','sale');
    insert into item_sale values('4rad','3/20/2012 13:04','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 13:05','hari','2400','sale');
    insert into item_sale values('4rad','3/20/2012 13:04','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 13:05','hari','23000','sale');
    insert into item_sale values('4rad','3/22/2012 17:13','dcdcd','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 17:14','hari','27000','sale');
    insert into item_sale values('4rad','3/22/2012 17:13','dcdcd','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 17:14','hari','1000','sale');
    insert into item_sale values('4rad','3/23/2012 11:05','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05','hari','14582.5','sale');
    insert into item_sale values('4rad','3/24/2012 15:49','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 15:50','hari','2300','sale');
    insert into item_sale values('4rad','3/24/2012 15:49','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 15:50','hari','2700','sale');

    Kindly help

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/e2579f51-3c5e-4ac3-a7e0-c97f0429aa71

    rgds


    HV

    samedi 31 mars 2012 03:13
  • hi mate,

    i tried your code and it gave me total perfectly. thanks

    I need to pass 3 variables

    1. Startdate

    2. End Date

    3. Categoryname 

    and the result of the SP should be based on the above values passed, can you help me with the tweak of your code pls.

    rgds


    HV

    samedi 31 mars 2012 03:21
  • The SP first does not give me a correct total....

    More exactly what is wrong?

    secondly when i run from my VB.net code it gives me an error as described in the link.

    So there are some errors in the VB code you have in that forum. Before I explain where the errors are, I like to make a statement. What you get here is assistance. That is, we give you an outline for a solution. Sometimes that may be a complete script, but this is very important: at no point we relieve you from the chores of thinking. You cannot just take the code as-is, you need make some effort to understand it. OK, so the XML PATH bit is a bit of mumbo jumbo and you can consider it as such. But the rest you need to have some understanding of. If not, you need to tell your manager or client that you don't have the knowledge for the task and they need to find someone else. After all, you presumably get paid to produce this code - we don't get paid to help you. Which is alright by me as long as you try to learn something from the advice we give you.

    OK, so back to the technical bit. In your VB code you have:

            cmd.Parameters.Add("@repeatpart", SqlDbType.NVarChar, 100)
            cmd.Parameters("@repeatpart").Value = ""
            cmd.Parameters.Add("@SQL", SqlDbType.NVarChar, 100)
            cmd.Parameters("@SQL").Value = ""
            cmd.Parameters.Add("@nlmarker", SqlDbType.Char, 6)
            cmd.Parameters("@nlmarker").Value = ""

    But @repeatpart, @SQL and @nlmarker are local parameters in your stored procedure, and not parameters.

    As I understand the thread in the VB forum, you don't get an error message; you simply do not get any rows back. Your post is that forum is also confusing, because you say you have a stored procedure, but you post a batch of SQL code which is not a stored procedures. Which opens the question of how the parameter list looks like.

    In any case, below there is my complete repro script that I have used to test my solutions. It creates the table (where I had to drop the IDENTITY property to get the INSERT to work), it includes your INSERT statements (where I had to drop the last value 'sale') to get them to work. Then comes my query batch, and the end I drop the table (so that I can easily run the script again).

    I suggest that you compose a complete repro of the same kind which demonstrates your problems. That is, instead of a batch, you should have your stored procedure and the EXEC statement to call it. Test the script in tempdb (so that you don't mess up your real table) before you post it.

    CREATE TABLE [dbo].[item_sale](
       [id] [int]  NOT NULL,
       [shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [tarik] [datetime] NULL,
       [kharidarname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [gaamname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [ItemName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [utpadaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [paakvistar] [float] NULL,
       [banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [vechanbhavperkl] [float] NULL,
       [vechanjatho] [float] NULL,
       [kulkimmat] [float] NULL,
       [BillNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [sale_comm] [float] NULL,
       [rahat_amt] [float] NULL,
       [entry_dt] [datetime] NULL,
       [entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [Total_Amt] [float] NULL,
     CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED
    (
       [id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    go
    insert into item_sale values('34','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 1:04:00 PM','hari','3000');
    insert into item_sale values('35','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','2400');
    insert into item_sale values('36','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','23000');
    insert into item_sale values('37','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf ','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','27000');
    insert into item_sale values('38','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf ','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','1000');
    insert into item_sale values('39','4rad','3/23/2012 11:05:35 AM','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko ','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05:00 AM','hari','14582.5');
    insert into item_sale values('41','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2300');
    insert into item_sale values('42','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2700');
    go
    SELECT  * FROM item_sale ORDER BY BillNo, id
    go
    DECLARE @repeatpart nvarchar(MAX),
            @SQL nvarchar(MAX),
            @nlmarker char(6)

    SELECT @nlmarker  = '<CRNL>'

    ; WITH numbered AS (
       SELECT ItemName,
              rn = row_number() OVER(ORDER BY ItemName)
       FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
    )
    SELECT @repeatpart =
       (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
        FROM   numbered
        FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    -- Replace @nlmarker with comma, CR-LF and an indent.
    SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

    -- Compose the final SQL.
    SELECT @SQL =
    'SELECT BillNo, kharidarname, gaamname,
    ' + @repeatpart + ' SUM(Total_Amt) AS Total_Amt_BillNo
    FROM    item_sale
    GROUP   BY BillNo, kharidarname, gaamname WITH ROLLUP
    HAVING grouping(BillNo) = 1 AND grouping(kharidarname) = 1 AND grouping(gaamname) = 1 OR
           grouping(BillNo) = 0 AND grouping(kharidarname) = 0 AND grouping(gaamname) = 0
    '

    print @SQL -- test
    execute(@SQL) 
    go

    drop table item_sale


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    samedi 31 mars 2012 09:27
  • hello,

    thanks, but im not able to pass the variables as i mentioned above, can you help me tweak the code pls.

    rgds

    hari


    HV

    samedi 31 mars 2012 16:58
  • thanks, but im not able to pass the variables as i mentioned above, can you help me tweak the code pls.

    Only if you help me to help you. Please see my earlier post of today for I want from you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    samedi 31 mars 2012 17:17
  • Hi,
    You should go with PIVOT TABLE.

    dimanche 1 avril 2012 09:46
  • Im not able to understand what details you need from me. Can you kindly tell me what exactly you need please

    Coz i need to pass 3 variables in your SP as mentioned below

    1. Startdate

    2. End Date

    3. Categoryname 

    rgds



    HV

    lundi 2 avril 2012 03:38
  • Can you please start a new thread on this topic. Post the procedure as you have now and what exactly is still not clear.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    lundi 2 avril 2012 03:42
    Modérateur
  • Im not able to understand what details you need from me. Can you kindly tell me what exactly you need please

    I feel that we have an uphill battle. I my long post of Saturday, March 31, 2012 9:27 AM, which Naomi marked as an answer, I commented on several things. I also gave a full script that you could run and see the result. I asked for a similar script from you to show what problems you have.

    I see you have started a new thread, but I don't have time to look into it tonight. And I will not mark the thread in my newsreader, so it is not likely that I will see it again. (I will see posts in this thread.) I see now that there is a second table which was not in the plot before.

    I get an uncanny feeling of a neverending story.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    lundi 2 avril 2012 22:25
  • no no wait...

    Im sorry about it.... there are a few things which im not able to understand coz m new to this...
    ill send you my code where i face problems when i get back to my desk....

    One more help which i need in your code is how can i make the itemnames as the header of the results, reason is that i save on 1 column for each itemname


    rgds


    HV

    mercredi 4 avril 2012 05:42
  • One more help which i need in your code is how can i make the itemnames as the header of the results, reason is that i save on 1 column for each itemname

    That's simple: in

    SELECT @repeatpart =
       (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
               ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
                    ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
        FROM   numbered
        FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    replace

       AS ItemName' + ltrim(str(rn))

    with

       AS ' + quotename(ItemName)

    And then you can drop the row_number thing - the only reason it was there because you asked for the colunm headers to be ItemName1, ItemName2 and so on.

    It does help if you present all your requirements from the beginning.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    mercredi 4 avril 2012 07:36
  • thanks 

    but not this way...

    The itemname column should not be there, but instead become the heading of qty and amt so it will look like

    "itemname qty" and "itemname amt"

    rgds



    HV

    jeudi 5 avril 2012 03:11
  • The itemname column should not be there, but instead become the heading of qty and amt so it will look like

    "itemname qty" and "itemname amt"


    In a haste I overlooked that part. However, I am sure that you can make that out on your own with some effort.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    jeudi 5 avril 2012 07:18
  • thanks,

    i wud definately take efforts for this, but im new to this. your help will be appreciated

    thanks and rgds


    HV

    samedi 7 avril 2012 04:12
  • i wud definately take efforts for this, but im new to this. your help will be appreciated

    And if you only work with solutions we spoonfeed you, you will remain new in this profession for your all life. You have gotten a couple of scripts from us. Pick them apart. Try to understand what is going on. If there is something that is obscure to you, ask.

    My ultimate aim hanging out in these forums, is not to give free consulting, least of all to people who are paid to do their jobs. Instead, I hope to be a mentor so that I can help you to be more successful in your job.

    Really great moments for me is then I help someone to walk the first mile, and that person then is able to find a good solution to his problem.

    So that is the question: are you willing to learn, or do you just want a solution that works, but you don't understand?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    samedi 7 avril 2012 09:27