CODEWITHSUNDEEP
c#sqldatagridviewcursors
misty
misty

Reputation: 123

Using SQL cursor for displaying data from database

I am making C# Windows Form Application, and it is connected to database.
Theme is: Bookshop.

In one of application's forms, there is DataGridView, which displays information about every book in shop.
In database, one book is unique, with its ISBN, and different books, in that context, can have same name.
Also, books can have many authors.
Meaning, when I make query that displays all books, it lists same book more than one time, to display all authors from that book.
What I want is, of course, to list all authors in one column, in one line, for one book.

I have been told that this could be done with cursors.
But, I can't imagine how to use them.
I don't want exact code, I just need some guidelines to solve this problem.

Also, is there maybe a better way to do this, then to use cursors?

Upvotes: 0

Views: 1085

Answers (3)

Reboon
Reboon

Reputation: 578

Here is a finished code example how you could do it (also with stuff as already suggested by others). Is it that what you were looking for?

-- declare variables

declare @ParamterCurrencyAmount numeric(26, 2),
        @ParameterRollingVisitSum int


-- declare table variable

declare @Books table
(
    ISBN int not null primary key,
    BookName varchar(255) not null
)

-- declare table variable

declare @Authors table
(
    AuthorID int primary key not null,
    AuthorName varchar(255) not null
)

-- declare table variable

declare @BookAuthorRelations table
(
    BookAuthorRelations int not null primary key,
    ISBN int not null,
    AuthorID int not null
)


-- insert sample data 

insert into @Books
(
    ISBN,
    BookName
)
select 1000, 'Book A' union all
select 2000, 'Book B' union all
select 3000, 'Book C'

insert into @Authors
(
    AuthorID,
    AuthorName
)
select 1, 'Jack' union all
select 2, 'Peter' union all
select 3, 'Donald'

insert into @BookAuthorRelations
(
    BookAuthorRelations,
    ISBN,
    AuthorID
)
select 1, 1000, 1 union all
select 2, 1000, 2 union all
select 3, 1000, 3 union all
select 4, 2000, 1 union all
select 5, 2000, 2 union all
select 6, 3000, 1


-- get books (with stuff)

select distinct Books.BookName,
stuff(
        (
            select distinct ', ' + Authors.AuthorName
            from @Authors Authors,
            @BookAuthorRelations BookAuthorRelations
            where BookAuthorRelations.AuthorID = Authors.AuthorID
            and Books.ISBN = BookAuthorRelations.ISBN
            for xml path(''), type
        ).value('.', 'NVARCHAR(MAX)') 
    , 1, 2,'') data
from @Books Books

Upvotes: 1

George Dando
George Dando

Reputation: 444

FOR XML

Is the way to get a column into a comma separated list normally:

How to get column values in one comma separated value

Obviously it doesn't have to be a comma separating them, you could put CHAR(13) or whatever you need.

Upvotes: 2

oratom
oratom

Reputation: 291

If you´re working on an Oracle-DB, you might use the LISTAGG - function like this:

SELECT listagg(a.author_name, ',') WITHIN GROUP (ORDER BY b.isin) names 
  FROM books b
  join book_authors ba on ba.bookId = b.bookId
  join authors a on a.authorId = ba.authorid

Upvotes: 0

Related Questions