locked
how to add row cell data in one row RRS feed

  • Question

  • USE []
    GO
    /****** Object:  Table [dbo].[ParaClassification]    Script Date: 11/25/2013 13:42:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ParaClassification](
    	[Para_Id] [int] IDENTITY(1,1) NOT NULL,
    	[ParagraphText] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[ParagraphRow] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[Positive_Value] [int] NULL,
    	[Negative_Value] [int] NULL,
    	[ClassName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    
    
    
    select sum(Positive_Value), sum(Negative_Value)
    from ParaClassification
    where ClassName='communication'
    
    

    the output is 
    2	0
    
    i want to also concatenate the ParagraphText column 
    
    1 Audi A4 1.8 TFSI Multitronic car Mileage is best.	  
    	0	0	carVersion
    					
    					
    4	economy of the car is 2.13 lakhs.
    Its Conventional door is good enought. 0	0	carVersion
    
    this are two row that i want concatenate the paragraphtext 
    
    plz reply


    • Moved by Olaf HelperMVP Monday, November 25, 2013 8:36 AM Moved from "SQL Database Engine" to a more related forum
    Monday, November 25, 2013 8:20 AM

Answers

  • thanks  but also i want the query should display the classname

    reply

    I also corrected a join that was missing in the previous query, enjoy.


    SELECT        SUM(Positive_Value) AS PosV, SUM(Negative_Value) AS NegV, (
    SELECT STUFF
    (
       ( SELECT  ',' + t1.ParagraphText 
        FROM ParaClassification t1     
        WHERE t1.car_id = p1.car_id and t1.classname = p1.ClassName
        ORDER BY t1.ParagraphRow
          FOR XML PATH('') , TYPE
        ).value('.', 'VARCHAR(MAX)') ,1,1,'')), ClassName
    FROM     ParaClassification p1 
      GROUP BY p1.ClassName, p1.car_id
    

    Monday, November 25, 2013 10:50 AM

All replies

  • Your output is not readable, so your question.

    Could you please provide some sample data in your table and desired output please.

    Monday, November 25, 2013 8:25 AM
  • It is not clear what desired result is... But have you tried adding ParagraphText  to the output and having GROUP BY clause by that col?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, November 25, 2013 8:43 AM
    Answerer
  • The table data is

    para_id        paragarpagText                             positive_value              negative_value          classname

    1 Audi A4 1.8 TFSI Multitronic car Mileage is best.        
    0                          0                          carVersion
    2 Its engine is bad. Its engine is                                    1                          0                            NULL
    3 USB Compatibility is notpoor.
                                       2                            0                 communication
    4 economy of the car is 2.13 lakhs.                               
    0                          0                       carVersion
    5 economy of the car is 2.13 lakhs.
    Its Conventional door is good enought.
    Its support Lumbar Support seat.
                         0                           1                  NULL

    6
    Its Conventional door is good enought. 
    Its support Lumbar Support seat.

                0                                     0                    NULL

    You can see over here className carVersion is come in two time. The query should be sum of positive_value,sum of negative_value and concatenate of paragraphText



    Monday, November 25, 2013 9:31 AM
  • How do you store information related to an BMW car for example? 

    Otherwise you could try something like :

    SELECT        SUM(Positive_Value) AS Expr1, SUM(Negative_Value) AS Expr2, (
    SELECT STUFF
    (
       ( SELECT  ',' + t1.ParagraphText 
        FROM ParaClassification t1     
        WHERE t1.car_id = p1.car_id
        ORDER BY t1.ParagraphRow
          FOR XML PATH('') , TYPE
        ).value('.', 'VARCHAR(MAX)') ,1,1,''))
    FROM     ParaClassification p1
      WHERE (ClassName = 'carVersion')
      GROUP BY p1.ClassName, p1.car_id

    where car_id would be a car id or something like this ( you don't need to have that group by but well...it looks weird without it).

    Monday, November 25, 2013 10:17 AM
  • thanks  but also i want the query should display the classname

    reply

    Monday, November 25, 2013 10:32 AM
  • thanks  but also i want the query should display the classname

    reply

    I also corrected a join that was missing in the previous query, enjoy.


    SELECT        SUM(Positive_Value) AS PosV, SUM(Negative_Value) AS NegV, (
    SELECT STUFF
    (
       ( SELECT  ',' + t1.ParagraphText 
        FROM ParaClassification t1     
        WHERE t1.car_id = p1.car_id and t1.classname = p1.ClassName
        ORDER BY t1.ParagraphRow
          FOR XML PATH('') , TYPE
        ).value('.', 'VARCHAR(MAX)') ,1,1,'')), ClassName
    FROM     ParaClassification p1 
      GROUP BY p1.ClassName, p1.car_id
    

    Monday, November 25, 2013 10:50 AM