Big problem with table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Big problem with table

Author

Topic

pee2002
Starting Member

16 Posts

Posted - 2009-07-21 : 12:09:11

ER Model:
http://img22.imageshack.us/img22/7292/modeloer.jpg

I´ve got this:

And want this:

(Paint image.. lol)

Anyone knows how to build a stored procedure who does that or a view?

Thanks!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-21 : 12:36:10

Maybe something like:

SELECT Titulo
    ,LEFT(Nome, LEN(Nome) - 2) AS Nome
    ,LEFT(Categoria, LEN(Categoria) - 2) AS Categoria
FROM
(    
    SELECT T1.Titulo
        ,(
            SELECT T2.Nome + ' | ' AS "text()"
            FROM YourTable T2
            WHERE T2.Titulo = T1.Titulo
            GROUP BY T2.Nome
            ORDER BY T2.Nome DESC
            FOR XML PATH('')
        ) AS Nome
        ,(
            SELECT T3.Categoria + ' | ' AS "text()"
            FROM YourTable T3
            WHERE T3.Titulo = T1.Titulo
            GROUP BY T3.Categoria
            ORDER BY T3.Categoria DESC
            FOR XML PATH('')
        ) AS Categoria
    FROM YourTable T1
    GROUP BY T1.Titulo
) D

pee2002
Starting Member

16 Posts

Posted - 2009-07-21 : 13:21:12

Hi Ifor!

Thanks for the help, but i dont know what to replace in "YourTable".
I forgot to say that the first querie is already a series of tables:

SELECT     dbo.Livro.Titulo, dbo.Autor.Nome, dbo.Categoria.Categoria
FROM         dbo.Autor INNER JOIN
                      dbo.LivroAutor ON dbo.Autor.IDAutor = dbo.LivroAutor.IDAutor INNER JOIN
                      dbo.Livro ON dbo.LivroAutor.IDLivro = dbo.Livro.IDLivro INNER JOIN
                      dbo.LivroCategoria ON dbo.Livro.IDLivro = dbo.LivroCategoria.IDLivro INNER JOIN
                      dbo.Categoria ON dbo.LivroCategoria.IDCategoria = dbo.Categoria.IDCategoria

Maybe this help more.

Thanks in advance!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-22 : 05:11:56

The simplest way would be to put your SELECT statement in a CTE:

;WITH YourTable(Titulo, Nome, Categoria)
AS
(
    SELECT    dbo.Livro.Titulo
            ,dbo.Autor.Nome
            ,dbo.Categoria.Categoria
    FROM dbo.Autor
        INNER JOIN dbo.LivroAutor
            ON dbo.Autor.IDAutor = dbo.LivroAutor.IDAutor
        INNER JOIN dbo.Livro
            ON dbo.LivroAutor.IDLivro = dbo.Livro.IDLivro
        INNER JOIN dbo.LivroCategoria
            ON dbo.Livro.IDLivro = dbo.LivroCategoria.IDLivro
        INNER JOIN dbo.Categoria
            ON dbo.LivroCategoria.IDCategoria = dbo.Categoria.IDCategoria
)
SELECT Titulo
    ,LEFT(Nome, LEN(Nome) - 2) AS Nome
    ,LEFT(Categoria, LEN(Categoria) - 2) AS Categoria
FROM
(    
    SELECT T1.Titulo
        ,(
            SELECT T2.Nome + ' | ' AS "text()"
            FROM YourTable T2
            WHERE T2.Titulo = T1.Titulo
            GROUP BY T2.Nome
            ORDER BY T2.Nome DESC
            FOR XML PATH('')
        ) AS Nome
        ,(
            SELECT T3.Categoria + ' | ' AS "text()"
            FROM YourTable T3
            WHERE T3.Titulo = T1.Titulo
            GROUP BY T3.Categoria
            ORDER BY T3.Categoria DESC
            FOR XML PATH('')
        ) AS Categoria
    FROM YourTable T1
    GROUP BY T1.Titulo
) D

but the following will probably be more efficient:

SELECT Titulo
    ,LEFT(Nome, LEN(Nome) - 2) AS Nome
    ,LEFT(Categoria, LEN(Categoria) - 2) AS Categoria
FROM
(
    SELECT L.Titulo
        ,(
            SELECT A.Nome + ' | ' AS "text()"
            FROM dbo.Autor A
                JOIN dbo.LivroAutor LA
                    ON A.IDAutor = LA.IDAutor
            WHERE LA.IDLivro = L.IDLivro
            --GROUP BY A.Nome
            ORDER BY A.Nome DESC
            FOR XML PATH('')
        ) AS Nome
        ,(
            SELECT C.Categoria + ' | ' AS "text()"
            FROM dbo.Categoria C
                JOIN dbo.LivroCategoria LC
                    ON C.IDCategoria = LC.IDCategoria
            WHERE LC.IDLivro = L.IDLivro
            --GROUP BY C.Categoria
            ORDER BY C.Categoria DESC
            FOR XML PATH('')
        ) AS Categoria
    FROM dbo.Livro L
    --GROUP BY L.Titulo
) D

pee2002
Starting Member

16 Posts

Posted - 2009-07-22 : 18:18:25

WoW !!

Perfect! It works flawless..

Thank you soooo much!

pee2002
Starting Member

16 Posts

Posted - 2009-07-22 : 19:10:23

Ifor,

For the final step i need to include the "IDLivro", "Posicao" and "Classificacao" (Just like the image bellow). I spend 2 hours around your code but its way to advanced for me :(

Can you do that?

Thanks a lot!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-23 : 05:52:13

[code]SELECT Titulo
,LEFT(Nome, LEN(Nome) - 2) AS Nome
,LEFT(Categoria, LEN(Categoria) - 2) AS Categoria
,Posicao
,Classificacao
FROM
etc
[/code]

pee2002
Starting Member

16 Posts

Posted - 2009-07-23 : 06:50:01

It doesnt work :(

And it has some logic because the FROm bellow is relative to a table that those columns doest exist.

I just need that. Please, help me

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-23 : 08:14:07

Assuming Posicao and Classificacao are columns in dbo.Livro:


SELECT Titulo
	,LEFT(Nome, LEN(Nome) - 2) AS Nome
	,LEFT(Categoria, LEN(Categoria) - 2) AS Categoria
	,Posicao
    ,Classificacao
FROM
(
	SELECT L.Titulo
		,(
			SELECT A.Nome + ' | ' AS "text()"
			FROM dbo.Autor A
				JOIN dbo.LivroAutor LA
					ON A.IDAutor = LA.IDAutor
			WHERE LA.IDLivro = L.IDLivro
			ORDER BY A.Nome DESC
			FOR XML PATH('')
		) AS Nome
		,(
			SELECT C.Categoria + ' | ' AS "text()"
			FROM dbo.Categoria C
				JOIN dbo.LivroCategoria LC
					ON C.IDCategoria = LC.IDCategoria
			WHERE LC.IDLivro = L.IDLivro
			ORDER BY C.Categoria DESC
			FOR XML PATH('')
		) AS Categoria
		,L.Posicao
		,L.Classificacao
	FROM dbo.Livro L
) D

pee2002
Starting Member

16 Posts

Posted - 2009-07-23 : 15:33:11

Awesome! Works flawless!

You are THE man of SQL !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources