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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    thanks  but also i want the query should display the classname

    reply

    Monday, November 25, 2013 10:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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