Solved

sql view

Posted on 2013-06-13
17
235 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
  • 9
  • 8
17 Comments
 
LVL 48

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 48

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
 
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 48

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 48

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 48

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 48

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 48

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 48

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 48

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now