sql view

Hi CaptainGiblets, trust you are well.

How long is the comments field? (and what is the data type?)
Is there a limit you wish to apply (e.g. if there was 50 comments on one item would you want to display all 50? or a smaller number).

For strings to be displayed in one column there is some form of 'concatenation' to be performed - how would you like each comment separated? (e.g. will a comma be enough?)

thanks, Paul

ideally a separate line to say 2 Enters on a keyboard. they are ntext and could have up to maybe 1000 characters in them. I would like to display as much information as possible. however most are max of say around 100 characters.

There wont be more than say 5-6 for each record.

NTEXT!
oh dear

(ntext went the way of button-up boots, it's effectively "deprecated")

anyway, here are 2 technical alternatives, not yet taking ntext into consideration - nor your wishlist on carriage returns - I've taken the liberty of adding the date to each comment (as I suspect that's needed). These might not yet work without some additional effort, but anyway its a start:

-- correlated subquery/for xml path
      SELECT
            main.name
          , main.[Trading Name]
          , main.[Sales Executive]
          , main.[Status]
          , stuff((
                  SELECT ' [' +  convert(varchar, your_data_here ,3) + '] ' + [comments]
                  FROM Table2 AS dets
                  WHERE main.id = dets.reference
                  ORDER BY dets.[date] DESC
                  FOR XML PATH('')
                      , TYPE
                  ).value('.', 'varchar(max)'), 1, 1, '') AS [comments]
      FROM Table2 AS main
      GROUP BY routing
;
-- cross apply, for xml path
	
        SELECT
            main.name
          , main.[Trading Name]
          , main.[Sales Executive]
          , main.[Status]
          , CA.comments
        FROM Table2 AS main
        CROSS APPLY (
                SELECT 
                    stuff((
                            SELECT ' [' +  convert(varchar, your_data_here ,3) + '] ' + [comments]
                            FROM Table2 AS dets
                            WHERE main.id = dets.reference
                            ORDER BY dets.[date] DESC
                            FOR XML PATH('')
                                , TYPE
                            ).value('.', 'varchar(max)'), 1, 1, '')
            ) AS CA(comments)

Select allOpen in new window

there should be no functional difference between these, there just might be a very slight performance gain using a cross apply.

Would you have some sample data?

I am trying to run it now but what do I put in this column?

SELECT ' [' +  convert(varchar, your_data_here ,3) + '] ' + [comments]


where it says your_data_here is that the cokumns that I want pulled from table1? I just don't understand what value I am meant to be converting is it the date column?

apologies, the date field goes there (I thought I had done that - obviously not)

I am not sure what the routing group by reference is however i have amended the column names to match the full database and tried to run the code and I have got this now

-- correlated subquery/for xml path
      SELECT
            tblcustomerdetails.[name]
          , tblcustomerdetails.[Trading as Name]
          , tblcustomerdetails.[SalesPerson]
          , tblcustomerdetails.[Internal Status]((
                  SELECT ' [' +  convert(varchar, Date ,3) + '] ' + [comment]
                  FROM tblcomments_new AS dets
                  WHERE tblcustomerdetails.Auto = dets.Auto
                  ORDER BY dets.[date] DESC
                  FOR XML PATH('')
                      , TYPE
                  ).value('.', 'varchar(max)'), 1, 1, '') AS [comment]
      FROM tblcustomerdetails
      GROUP BY Name, [trading as name], [salesperson]
;

Select allOpen in new window

this is throwing the error

---------------------------
Microsoft SQL Server Management Studio
---------------------------
SQL Execution Error.

Executed SQL statement: SELECT tblcustomerdetails.[name] , tblcustomerdetails.[Trading as Name] , tblcustomerdetails.[SalesPerson] , tblcustomerdetails.[Internal Status](( SELECT ' [' + CONVERT(varchar, Date ,3) + '] ' + [comment] FROM tblcomments_new AS dets WHERE tblcusto...
Error Source: .Net SqlClient Data Provider
Error Message: Cannot find either column "tblcustomerdetails" or the user-defined function or aggregate "tblcustomerdetails.Internal Status", or the name is ambiguous.
---------------------------
OK   Help  
---------------------------

The column tblcustomerdetails.[internal status] exists.

try this please

-- correlated subquery/for xml path
      SELECT
            dets.[name]
          , dets.[Trading as Name]
          , dets.[SalesPerson]
          , dets.[Internal Status]
          , STUFF ((
                  SELECT ' [' +  convert(varchar, comms.[Date] ,3) + '] ' + [comment]
                  FROM tblcomments_new AS comms
                  WHERE dets.Auto = comms.Auto
                  ORDER BY comms.[date] DESC
                  FOR XML PATH('')
                      , TYPE
                  ).value('.', 'varchar(max)'), 1, 1, '') AS [comment]
      FROM tblcustomerdetails as dets
      GROUP BY Name, [trading as name], [salesperson]
;

Select allOpen in new window

tblcustomerdetails now has the alias dets, and tblcomments_new has the alias comms
what was missing in your code was the commnd 'STUFF' by the way.

Ah ok, what does Stuff stand for? as I thought you were just showing me where I can add extra columns haha!

I have done that now  had to add a few more columns to the group by clause and now I get this

---------------------------
Microsoft SQL Server Management Studio
---------------------------
SQL Execution Error.

Executed SQL statement: SELECT dets.[name] , dets.[Trading as Name] , dets.[SalesPerson] , dets.[Internal Status] , STUFF (( SELECT ' [' + CONVERT(varchar, comms.[Date] ,3) + '] ' + [comment] FROM tblcomments_new AS comms WHERE dets.Auto = comms.Auto ORDER BY comms.[date] D...
Error Source: .Net SqlClient Data Provider
Error Message: The data types varchar and ntext are incompatible in the add operator.
---------------------------
OK   Help  
---------------------------


So I am guessing I will have to convert my ntext column to varchar / nvarchar before I will be able to run this?

I have converted to nvarchar and it works great! thanks for all your help

sorry to mither, but some of the fields are missing date columns because they are quite old, and I think that if either the date / user field are empty the comments are not being displayed. Is there a way around this?

yep, the isnull function should do it

-- correlated subquery/for xml path
      SELECT
            dets.[name]
          , dets.[Trading as Name]
          , dets.[SalesPerson]
          , dets.[Internal Status]
          , STUFF ((
                  SELECT ' [' +  isnull(comms.[Date], convert(varchar, comms.[Date] ,3) , 'undated') + '] ' + [comment] + char(13) + char(13) /* attempt to add 2 carriage returns */
                  FROM tblcomments_new AS comms
                  WHERE dets.Auto = comms.Auto
                  ORDER BY comms.[date] DESC
                  FOR XML PATH(''), TYPE
                  ).value('.', 'varchar(max)'), 1, 1, '') AS [comment]
      FROM tblcustomerdetails as dets
      GROUP BY
            dets.[name]
          , dets.[Trading as Name]
          , dets.[SalesPerson]
          , dets.[Internal Status]
;

Select allOpen in new window

STUFF (Transact-SQL) - curious word for a tsql function I'll grant you, but it wasn't an invitation to replace with <<whatever>>

& Thanks for the prompt award of points, much appreciated. Pleased I could help.

Out of interest - did those char(10)'s actually produce the wanted separation in SharePoint?
(it would be useful for me to know)

all the best...
Cheers, Paul

They didn't i'm afraid... sorry I didn't answer quicker. at the moment I have just added '-----' at the end so people can see the breaks.

>>They didn't  I'm afraid...

thanks for coming back on this. I'm not at all surprised - but it's good for me to know.

do you know any html?

instead of "char(10)" you could try "<br />" - all without quotes

or even 2 break lines together...

"<br /><br />"

However even this might not work - but it may be worth trying.

ps: Australia in World Cup 2014 :)

I have tried the html tags but with no success.

It just shows whatever is shown in the sql view when I execute it. So they will have to make do with this.

I will be in Rio for the WC but I am afraid I will be supporting England :p

damn you

how dare you be in Rio - and me not?

You can support England - that's fine. Brazil have it in the bag of course (they expect).