Solved

sql view

Posted on 2013-06-13
17
244 Views
Last Modified: 2013-06-20
I have 2 tables with the following setup

Table 1
Reference
Comment
User
Date
Unique ID

Table 2
Auto ID
Name
Trading Name
Sales Executive
Status

Table 2's Auto ID is a unique reference, users add comments for Table 2 into Table 1 and they are linked via Auto ID - Reference.

I now have a view on sharepoint and I want to be able to show all the comments for Table 2's record in 1 column, how is the easiest way I can go about doing this please?

I am using SQL 2008 Express.
0
Comment
Question by:CaptainGiblets
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243922
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
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39243961
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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243985
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)

Open 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?
0
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39244021
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?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244027
apologies, the date field goes there (I thought I had done that - obviously not)
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39244045
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]
;

Open 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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244061
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]
;

Open 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.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39244067
btw: regarding this: "ideally a separate line to say 2 Enters on a keyboard"

This may not be possible. SharePoint pushes it data to the end-user through a web browser - it is actually HTML that carries the data to the browser - and HTML does not recognize a carriage return. We can try to add some carriage returns into the data supplied - but I am doubtful it will then present as a new line in the SharePoint site.

see (end of) line 8
-- 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] + 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]
;

Open in new window

0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39244068
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?
0
 
LVL 6

Author Closing Comment

by:CaptainGiblets
ID: 39244079
I have converted to nvarchar and it works great! thanks for all your help
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39244097
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?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244130
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]
;

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244163
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
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39262026
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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39262342
>>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 :)
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39262356
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39262413
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).
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question