transformando várias linhas em uma só coluna

utilizando cursor e tabela virtual

concatenação de tabelas

neste exemplo vamos consultar uma tabela NxN Produto e Fornecedor, vamos listar todos os produtos e para cada produto vamos listar os seus fornecedores cadastrados, mas somente em uma única linha por produto separados por vírgula

neste exemplo também usaremos alguns recursos interessantes como tabela virtual (tabela temporária) e cursor

set nocount on
--tabelas para testes
create table produto (ID_PRODUTO int, NOM_PRODUTO varchar(50))
insert into produto (ID_PRODUTO, NOM_PRODUTO ) values (1, 'PRODUTO 1')
insert into produto (ID_PRODUTO, NOM_PRODUTO ) values (2, 'PRODUTO 2')
insert into produto (ID_PRODUTO, NOM_PRODUTO ) values (3, 'PRODUTO 3')
        
create table fornecedor(ID_FORNECEDOR int, NOM_FORNECEDOR varchar(50))
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (1, 'FORNECEDOR 1')
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (2, 'FORNECEDOR 2')
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (3, 'FORNECEDOR 3')
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (4, 'FORNECEDOR 4')
               
create table forn_prod (ID_PRODUTO int, ID_FORNECEDOR int)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (1, 1)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (2, 1)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (1, 2)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (2, 2)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (1, 4)
        
        
        
--tabela temporaria
create table #temp (NOM_PRODUTO varchar(50), NOM_FORNECEDOR varchar(4000))
        
--select distinct para buscar somente produtos que estejam na tabela forn_prod
--cursor x: produtos
declare x cursor for select distinct forn_prod.ID_PRODUTO, NOM_PRODUTO from forn_prod
                                inner join produto on produto.ID_PRODUTO = forn_prod.ID_PRODUTO
        --variaveis para o cursor x
        declare @ID_PRODUTO int
        declare @NOM_PRODUTO varchar(50)
        --variável para concatenar o nome dos fornecedores
        declare @NOM_FORNECEDOR_conc varchar(8000)
        open x
        fetch next from x into @ID_PRODUTO,@NOM_PRODUTO
        while @@fetch_Status=0
        begin
                --zerando variável de concatenação
                set @NOM_FORNECEDOR_conc = ''
                --cursor y: fornecedores relacionados com os produtos, vai concatenar os fornecedores na variável @NOM_FORNECEDOR_conc
                declare y cursor for select NOM_FORNECEDOR from fornecedor
                                        inner join forn_prod on forn_prod.ID_FORNECEDOR = fornecedor.ID_FORNECEDOR
                                        where forn_prod.ID_PRODUTO = @ID_PRODUTO
                --variavel para o cursor y
                declare @NOM_FORNECEDOR varchar(50)
                open y
                fetch next from y into @NOM_FORNECEDOR
                while @@fetch_Status=0
                begin
                        --concatenando fornecedores na variável @NOM_FORNECEDOR_conc
                        set @NOM_FORNECEDOR_conc = @NOM_FORNECEDOR_conc + @NOM_FORNECEDOR + ', '
        
                        --loop do cursor y
                        fetch next from y into @NOM_FORNECEDOR
                end
                --fim do cursor y
                close y
                deallocate y  
        
                --retira última virgula
                set @NOM_FORNECEDOR_conc = substring(@NOM_FORNECEDOR_conc, 1, len(@NOM_FORNECEDOR_conc)-1)
                --insere na tabela virtual
                insert into #temp (NOM_PRODUTO, NOM_FORNECEDOR )
                values (@NOM_PRODUTO, @NOM_FORNECEDOR_conc)
        
                --loop do cursor x
                fetch next from x into @ID_PRODUTO,@NOM_PRODUTO
        end
--fim do cursor x
close x
deallocate x
        
--consulta da tabela temporaria
select * from #temp
       
--apagando tabela temporaria
drop table #temp
--apagando tabelas para testes
drop table produto
drop table fornecedor
drop table forn_prod

Resultado:

NOM_PRODUTO                    NOM_FORNECEDOR

PRODUTO 1                      FORNECEDOR 1, FORNECEDOR 2, FORNECEDOR 4
PRODUTO 2                      FORNECEDOR 1, FORNECEDOR 2

AUTOR: "eriva_br"

Dúvidas, criticas, contribuições, correções e adições seram bem vindas. 

Gerando Somatórios

alguns tipos de totalização de dados

totalizando, somando dados:

no SQL Server, para estas operações de somar e totalizar podemos usar as seguintes funções:
- COMPUTE: realiza somatórios, nos agrupamentos divide os grupos em result-sets diferentes.
Podem ser usados outros operadores no compute: { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }

- ROLLUP: realiza somatórios, nos agrupamentos mantém em um único result-sets

- CUBE: realiza somatórios, nos agrupamentos mantém em um único result-sets e no final gera totais por grupos


Dados para testes:

set nocount on
declare @tab table (IDProduto char(5), Nome varchar(10), valor money)
insert into @tab (IDProduto, Nome, valor) values (1, 'Norte', 10)
insert into @tab (IDProduto, Nome, valor) values (1, 'Sul', 20)
insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 5)
insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 15)
insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 25)
insert into @tab (IDProduto, Nome, valor) values (1, 'Leste', 10)
insert into @tab (IDProduto, Nome, valor) values (3, 'Oeste', 15)
insert into @tab (IDProduto, Nome, valor) values (3, 'Oeste', 5)
insert into @tab (IDProduto, Nome, valor) values (3, 'Norte', 5)

Exemplo com COMPUTE:

select IDProduto, Nome, valor from @tab
                  order by IDProduto
                  compute sum(valor) by IDProduto
                  compute sum(valor)

resultado da query acima:

                  IDProduto Nome       valor                             
                  --------- ---------- --------------------- 
                  1              Norte    10.0000
                  1              Sul      20.0000
                  1              Leste    10.0000
                  
                                                           sum
                                                           =====================
                                                           40.0000
                  
                  
                  IDProduto Nome           valor                                 
                  --------- ---------- --------------------- 
                  2              Norte    25.0000
                  2              Norte    5.0000
                  2              Norte    15.0000
                  
                                                           sum
                                                           =====================
                                                           45.0000
                  
                  
                  IDProduto Nome           valor                                 
                  --------- ---------- --------------------- 
                  3              Oeste    15.0000
                  3              Oeste    5.0000
                  3              Norte    5.0000
                  
                                                           sum
                                                           =====================
                                                           25.0000
                  
                  
                                                           sum
                                                           =====================
                                                           110.0000

Exemplo com ROLLUP:

select 
                         case when (grouping(IDProduto)=1) then 'TOTAL'
                         else isnull(IDProduto, 'desconhecido') end as IDProduto, 
                         case when (grouping(Nome)=1) then 'SUB-TOTAL'
                         else isnull(Nome, 'desconhecido') end as Nome,
                         sum(valor) as valor 
                 from @tab
                 group by IDProduto, Nome 
                 WITH ROLLUP

resultado da query acima:

                IDProduto Nome       valor                               
                --------- ---------- --------------------- 
                1                Leste    10.0000
                1                Norte    10.0000
                1                Sul      20.0000
                1                SUB-TOTAL  40.0000
                2                Norte    45.0000
                2                SUB-TOTAL  45.0000
                3                Norte    5.0000
                3                Oeste    20.0000
                3                SUB-TOTAL  25.0000
                TOTAL    SUB-TOTAL  110.0000

Exemplo com CUBE:

select 
                        case when (grouping(IDProduto)=1) then 'TOTAL'
                        else isnull(IDProduto, 'desconhecido') end as IDProduto, 
                        case when (grouping(Nome)=1) then 'SUB-TOTAL'
                        else isnull(Nome, 'desconhecido') end as Nome,
                        sum(valor) as valor 
                from @tab
                group by IDProduto, Nome 
                WITH CUBE

resultado da query acima:

                IDProduto Nome       valor                               
                --------- ---------- --------------------- 
                1                Leste    10.0000
                1                Norte    10.0000
                1                Sul      20.0000
                1                SUB-TOTAL  40.0000
                2                Norte    45.0000
                2                SUB-TOTAL  45.0000
                3                Norte    5.0000
                3                Oeste    20.0000
                3                SUB-TOTAL  25.0000
                TOTAL    SUB-TOTAL  110.0000
                TOTAL    Leste    10.0000
                TOTAL    Norte    60.0000
                TOTAL    Oeste    20.0000
                TOTAL    Sul            20.0000

no exemplo podemos observar tb. o uso da função GROUPING, com esta função verificamos se o retorno refere-se a uma linha de sub-totais ou a uma linha de totais


AUTOR: "eriva_br"

Dúvidas, criticas, contribuições, correções e adições seram bem vindas. 

TOP dinâmico, usando SET ROWCOUNT

veremos como utilizar a função ROWCOUNT para realizar uma consulta com a quantidade de retorno de registros de forma dinâmica, definindo o quantidade de retorno de linhas por uma varável

definindo banco de dados para teste

--definindo banco de dados para teste
                use pubs;

select comum com o uso do TOP fixo

--select comum com o uso do TOP fixo
                select top 10 * from authors

caso tentarmos passar a quantidade a ser usada pelo TOP por uma variável, o sql server não aceita

declare @top int
                set @top = 3
                select top @top * from authors

ao executar a linha acima é retornado o seguinte erro:

Quote

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '@top'.

para contornar o erro acima poderiamos usar o EXEC e deixar a query em string, mas desta forma a execução perde performance (nada de anormal, mas perde)

declare @top int
                set @top = 3
                exec ('select top ' + @top + ' * from authors')

Uma forma de conseguir o mesmo resultado sem usar o EXEC seria com o comando SET ROWCOUNT, ao setar o ROWCOUNT, são afetados somente as quantidades de registros definida para os comandos SELECT, DELETE, INSERT e UPDATE 
E com o ROWCOUNT tem-se a opção de ser passado a quantidade por variável

exemplo:

declare @top int
           set @top = 3
           
           set rowcount @top
           
           --somente serão retornados 3 registros
           select * from authors

testando no update:

--adicionando uma coluna de testes na tabela authors, seu valor padrão ficará como NULL
  alter table authors add ColunaTeste char(1)
  
  --definindo quantidade de registros a serem processados
  declare @top int
  set @top = 3
           
  set rowcount @top
           
  --fazendo update, repare que esta sem WHERE, mas serão executados somente 3 registros
  update authors set ColunaTeste = 'x'

para voltar a quantidade de registros a serem processados para o normal, ou seja, todos, basta setar o ROWCOUNT para ZERO (0)

set rowcount 0
  select * from authors

verifique que na nossa coluna de testes, somente foram processados no update 3 registros

para remover a coluna de testes

alter table authors drop column ColunaTeste

AUTOR: "eriva_br"

Dúvidas, criticas, contribuições, correções e adições seram bem vindas.