need help to construct a SQL Query to show top 3 results as one output

Hi,
I have a table name InvoiceNotes, I am looking to get 3 most recent notes given an invoice number, however, I am looking for the result on a single row. Is that possible? I am running it on MSSQL Server 2008 R2
Thanks!
Ho-Ching

InvoiceNumber  createuser        notes                createdate
1011                     John                   testnote1         2014-7-1-1:00PM
1011                 Steve                  testnote2         2014-7-1-2:00PM
1011                 Brenda               testnote3         2014-7-4-2:00PM
1011                 Megan               testnote4         2014-7-5-2:00PM
1012                 Megan               testnote5        2014-7-5-2:00PM


SELECT TOP 3  notes FROM InvoiceNotes WHERE InvoiceNumber  = 1011 ORDER BY createdate DESC

but I need the result to be one row such as:
             
testnote4@testnote3@testnote2
rskeptonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Do you want the notes in separate columns or a single column?
0
Pooja Katiyar VermaCommented:
In MySQL there is a function,
GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.
Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a;
OR
 SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

Similarly:
SELECT  InvoiceNumber  , Group_concat(notes) FROM InvoiceNotes group by InvoiceNumber  ;
0
PortletPaulfreelancerCommented:
I interpret this to mean one row and one column (with @ as the delimiter)

This is for Microsoft SQL Server
select
        inv.InvoiceNumber
      , ca1.top3invnotes
from invoices inv
cross apply (
              select
                    STUFF(
                      ( Select top 3 '@' + notes
                        from InvNotes
                        where InvNotes.InvoiceNumber = inv.InvoiceNumber
                        order by createdate DESC
                        FOR XML PATH('')
                      ),1,1,'')
              ) as ca1 (top3invnotes)

Open in new window

This produces:
| INVOICENUMBER |                  TOP3INVNOTES |
|---------------|-------------------------------|
|          1011 | testnote4@testnote3@testnote2 |
|          1012 |                     testnote5 |
|          1013 |                        (null) |



CREATE TABLE InvNotes
	([InvoiceNumber] int, [createuser] varchar(6), [notes] varchar(9), [createdate] varchar(15))
;
	
INSERT INTO InvNotes
	([InvoiceNumber], [createuser], [notes], [createdate])
VALUES
	(1011, 'John', 'testnote1', '2014-7-1-1:00PM'),
	(1011, 'Steve', 'testnote2', '2014-7-1-2:00PM'),
	(1011, 'Brenda', 'testnote3', '2014-7-4-2:00PM'),
	(1011, 'Megan', 'testnote4', '2014-7-5-2:00PM'),
	(1012, 'Megan', 'testnote5', '2014-7-5-2:00PM')
;

CREATE TABLE Invoices
	([InvoiceNumber] int)
;
	
INSERT INTO Invoices
	([InvoiceNumber])
VALUES
	(1011),
	(1012),
	(1013)
;

http://sqlfiddle.com/#!3/f42c4/2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rskeptonAuthor Commented:
Thanks guys!
PortletPaul, your solution works.
If it is not too much to ask, is there anyway to have the output to separate columns? That was my originally goal but I didn't know it may be possible.

Topnote1     Topnote2     Topnote3
testnote4      testnote3     testnote2
0
rskeptonAuthor Commented:
I should have been more clear on my question, my ultimate goal is to have the output looks like

Topnote1     Topnote2     Topnote3     TopCreatuser1   TopCreatuser2   TopCreatuser3
 testnote4      testnote3     testnote2    Megan                 Brenda                 Steve
0
PortletPaulfreelancerCommented:
ah! the importance of "expected result" :)

try this:
select
       InvoiceNumber
     , max(case when rn = 1 then notes end) as note1
     , max(case when rn = 2 then notes end) as note2
     , max(case when rn = 3 then notes end) as note3
     , max(case when rn = 1 then createuser end) as createuser1
     , max(case when rn = 2 then createuser end) as createuser2
     , max(case when rn = 3 then createuser end) as createuser3
from (
      
      select
              InvoiceNumber
            , createuser
            , notes
            , row_number() over(partition by InvoiceNumber
                                order by createdate DESC) as rn
      from InvNotes
     ) rns
where rn in (1,2,3)
group by
        InvoiceNumber
;

| INVOICENUMBER |     NOTE1 |     NOTE2 |     NOTE3 | CREATEUSER1 | CREATEUSER2 | CREATEUSER3 |
|---------------|-----------|-----------|-----------|-------------|-------------|-------------|
|          1011 | testnote4 | testnote3 | testnote2 |       Megan |      Brenda |       Steve |
|          1012 | testnote5 |    (null) |    (null) |       Megan |      (null) |      (null) |

http://sqlfiddle.com/#!3/f42c4/7

Open in new window

0
rskeptonAuthor Commented:
thanks guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.