Results showed in a new way
-
I have one select command what brings the following result:
Material Client Files
---------------------------
Paper Alex 123
Paper Gilbert 322
Paper Sandra 488
Water Alex 155
Water Sandra 471
Water Marcio 177
Iron Alex 178
Now, I need to show the result in the format bellow showed:
Material Alex Gilbert Sandra Marcio
---------------------------------------------
Paper 123 322 488
Water 155 471
Iron 178 177
Whats it the best way to do that?
Thanks since now.
Alex.
-
Here is one way, but it requires you know the data to setup the query:
declare @testtbl table (
Material varchar(10),
Client varchar(10),
Files smallint
)
insert into @testtbl values('Paper','Alex',123)
insert into @testtbl values('Paper','Gilbert',322)
insert into @testtbl values('Paper','Sandra',488)
insert into @testtbl values('Water','Alex',155)
insert into @testtbl values('Water','Sandra',471)
insert into @testtbl values('Water','Marcio',177)
insert into @testtbl values('Iron','Alex',178)
select * from @testtbl
select
Material,
sum(case when Client = 'Alex' then Files else 0 end) as 'Alex',
sum(case when Client = 'Gilbert' then Files else 0 end) as 'Gilbert',
sum(case when Client = 'Sandra' then Files else 0 end) as 'Sandra',
sum(case when Client = 'Marcio' then Files else 0 end) as 'Marcio'
from
@testtbl
group by
Material
hth
-
If you are using SS 2005, this is another way using pivot
Create
table #testtbl (
Material
varchar(10),
Client
varchar(10),
Files
smallint
)
insert
into #testtbl values('Paper','Alex',123)
insert
into #testtbl values('Paper','Gilbert',322)
insert
into #testtbl values('Paper','Sandra',488)
insert
into #testtbl values('Water','Alex',155)
insert
into #testtbl values('Water','Sandra',471)
insert
into #testtbl values('Water','Marcio',177)
insert
into #testtbl values('Iron','Alex',178)
GO
SELECT
Material, [Alex] AS Alex, [Gilbert] AS Gilbert, [Sandra] AS Sandra, [Marcio] AS Marcio
FROM
(
SELECT Material, Client, Files FROM #testtbl) p
PIVOT
(
SUM
(Files)
FOR
Client IN
(
[Alex], [Gilbert], [Sandra], [Marcio])
)
AS pvt
ORDER
BY Material
DROP
TABLE #testtbl
-
Friends, good morning.
Thanks for the answers, but in my case, the registers are not just Alex, Gilbert, Sandra and Marcio.
In another words, the actual result:
Material Client Files
---------------------------
Paper Alex 123
Paper Gilbert 322
Paper Sandra 488
Water Alex 155
Water Sandra 471
Water Marcio 177
Iron Alex 178
Tomorrow will be:
Material Client Files
---------------------------
Paper Alex 123
Paper Gilbert 322
Paper Sandra 488
Water Alex 155
Water Sandra 471
Water Marcio 177
Iron Alex 178
Water Livio 877
Iron Edna 188
They will grow up with time, do you have another sugestion?
Thanks a lot since now !
Alex Sandro
31 8836-6159
-
This is where your knowledge of the data is important. Are all the possible registers known in advance, or are each of them an unknown until there is an entry in the system? Is there a seperate table that lists all the possible registers?
Need to know this to be able to come up with a solution.
Lynn
-
Friend,
First of all, thanks in advice for your help!
The field is unknowed until there is an entry in the system. The result came from another table, its the key between the two tables.
Whats the best way to solve this problem, friend?
Alex
-
I turned to dynamic sql, but I got a possible solution. There may be other ways to do this, but I will leave that to the SQLServerCentral community to find and provide those:
create table ##testtbl (
Material varchar(10),
Client varchar(10),
Files smallint
)
insert into ##testtbl values('Paper','Alex',123)
insert into ##testtbl values('Paper','Gilbert',322)
insert into ##testtbl values('Paper','Sandra',488)
insert into ##testtbl values('Water','Alex',155)
insert into ##testtbl values('Water','Sandra',471)
insert into ##testtbl values('Water','Marcio',177)
insert into ##testtbl values('Iron','Alex',178)
declare @VarStr varchar(8000)
set @VarStr = 'select '
select @VarStr = @VarStr + 'sum(case when Client = ''' +
Client + ''' then Files else 0 end) as ''' +
Client + ''',' from (select distinct Client from ##testtbl) dt --##testtbl
set @VarStr = substring(@VarStr, 1, len(@VarStr) - 1) + ' from ##testtbl group by Material'
--select @VarStr
exec (@VarStr)
drop table ##testtbl
go
create table ##testtbl (
Material varchar(10),
Client varchar(10),
Files smallint
)
insert into ##testtbl values('Paper','Alex',123)
insert into ##testtbl values('Paper','Gilbert',322)
insert into ##testtbl values('Paper','Sandra',488)
insert into ##testtbl values('Water','Alex',155)
insert into ##testtbl values('Water','Sandra',471)
insert into ##testtbl values('Water','Marcio',177)
insert into ##testtbl values('Iron','Alex',178)
insert into ##testtbl values('Water','Livio',877)
insert into ##testtbl values('Iron','Edna',188)
declare @VarStr varchar(8000)
set @VarStr = 'select '
select @VarStr = @VarStr + 'sum(case when Client = ''' +
Client + ''' then Files else 0 end) as ''' +
Client + ''',' from (select distinct Client from ##testtbl) dt --##testtbl
set @VarStr = substring(@VarStr, 1, len(@VarStr) - 1) + ' from ##testtbl group by Material'
--select @VarStr
exec (@VarStr)
drop table ##testtbl
go
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply