row to column - 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

General SQL Server Forums

New to SQL Server Programming

row to column

Author

Topic

andreacaffy
Starting Member

6 Posts

Posted - 2011-03-30 : 10:26:50

I have a view:

Product List Price
pr1 1 6
pr1 2 7
pr1 3 6
pr2 [...]

it is possibile to create from this view a table made in this way?

product price1(linked to list1) price2 (list2) price3 (list3)
pr1 6 7 6

Any ideas?

I find this code, but I need to comply with my needs and I'm not able to do it.

I have a table with 18 fields, among them there are
ID_PRODUCT (code that identifies the product), PREZZOACQ (price of the product), ID_LIST (list of the product).
Now, if the product with ID_PRODUCT=1 it costs 1 in the list price 1, it is created a record (row). If the same product id_product=1 costs 2 in the list price 2 a new record it is created...etc.
if id_product=1 costs 1 in the list 3 it is created a new record.

That is for every product is created many records as the number of list price.

I would like to create many column PriceAcq as are the number of the list price so that the product with id_product=1 is visible in the table only one time but with more column (many priceacq as list price)

This is the code that I find... but something's wrong and it doesn't work.

CREATE TABLE [webazienda_CRSRL].[dbo].[Product_CR2] (
[TipoProdotto] nvarchar(50),
[ID_PRODUCT] int,
[CodiceBarre] nvarchar(20),
[IVA] nvarchar(5),
[Fornitore] int,
[NOVITA] bit,
[Cdes] nvarchar(50),
[Descrizione] nvarchar(MAX),
[CodProd] nvarchar(15),
[user] nvarchar(50),
[AMagazzino] nvarchar(100),
[LottoAppr] numeric(18, 3),
[Scortaminima] numeric(18, 3),
[Ordinato cliente] numeric(18, 3),
[PrezzoVendita1] numeric(18, 3),
[IDListino] int
) ON [PRIMARY]
GO

declare @max int, @min int
set @max = (select max(ID_PRODOTTO) from Prodotti_CR)
set @min = (select min(ID_PRODOTTO) from Prodotti_CR)

declare @totcols int, @counter int

set @totcols = (select count(*) from PRODOTTI_CR where ID_PRODOTTO = 232)

set @totcols = 7
set @counter = 1

declare @str varchar(50)
declare @sqlStr nvarchar(255)

while @counter <= @totcols
begin
set @str = 'prezzoacquisto'+ convert(varchar(5),@counter)

set @sqlStr = N'alter table Prodotti_CR2 add '+ @str + ' int null'
exec sp_executesql @sqlStr

set @counter = @counter + 1
set @str = ''
set @sqlStr = ''
end

insert into Prodotti_CR2([TipoProdotto],[ID_PRODOTTO],[CodiceBarre],[IVA],[Fornitore],[NOVITA],[Cdes],[Descrizione],[CodProd],[user],[AMagazzino],[LottoAppr],[Scortaminima],[Ordinato cliente],[PrezzoVendita1],[IDListino])

select distinct [TipoProdotto],[ID_PRODOTTO],[CodiceBarre],[IVA],[Fornitore],[NOVITA],[Cdes],[Descrizione],[CodProd],[user],[AMagazzino],[LottoAppr],[Scortaminima],[Ordinato cliente],[PrezzoVendita1],[IDListino] from Prodotti_CR

while @min <= @max
begin
set @counter = 1
while @counter <= @totcols
begin
set @str = 'prezzoacquisto'+ convert(varchar(5),@counter)

set @sqlStr = N'update Prodotti_CR2 set ' + @str + ' = (select PrezzoAcq from Prodotti_CR where idlistino = ' + convert(varchar(5),@min) + ')' +
')'
exec sp_executesql @sqlStr

set @counter = @counter + 1
set @min = @min + 1
set @str = ''
set @sqlStr = ''

end

end

Help me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources