March 2, 2008 at 7:33 pm
I have table which has values like this:
FirstName | LastName | ContractType
-------------------------------------------------
John | Smith | Fixed
John | Smith | TimeMaterial
John | Smith | CostType
John | Smith | Flexibly Priced
I want to create something like this
FirstName | LastName | ContractType
--------------------------------------------------
John | Smith | Fixed, TimeMaterial, CostType, Flexibly Priced
-Thanks
March 2, 2008 at 9:44 pm
Jeff Moden had a wonderful bit of magic with XML formatting commands which don't end up doing XML, etc to make CSV lists in SQL.
This ought to do what you want....
create table #myTable( firstname varchar(50), surname varchar(50), contractType varchar(50), primary key clustered(surname, firstname, contractType) )
insert into #myTable( firstname, surname, contractType )
select 'John', 'Smith', 'Fixed'
UNION ALL
select 'John', 'Smith', 'TimeMaterial'
UNION ALL
select 'John', 'Smith', 'CostType'
UNION ALL
select 'John', 'Smith', 'Flexibly Priced'
select
firstname,
surname,
stuff(
(
select ', ' + myTableX.ContractType
from #myTable myTableX
where myTableX.firstname = myTable.Firstname
and myTableX.surname = myTable.surname
FOR XML PATH('')
),
1, 2, '')
from #myTable myTable
group by firstname, surname
drop table #myTable
Something I should point out however - I haven't tested for multiple employees (but it ought to work without problem).
Secondly, for what purpose do you need the CSV? Often such formatting is best done in GUI / reporting although it can sometimes be easier if it's done at the SQL level 🙂
March 3, 2008 at 6:17 am
I hope this helps
select
FirstName
, lastName
, max(Case when ContractType = 'Fixed' then ContractType else '0' end)
+ ',' + max(Case when ContractType = 'TimeMaterial' then ContractType else '0' end)
+ ',' + max(Case when ContractType = 'CostType' then ContractType else '0' end)
+ ',' + max(Case when ContractType = 'Flexibly Priced' then ContractType else '0' end) as ContractType
from test1
where ContractType in('Fixed','TimeMaterial','CostType','Flexibly Priced')
group by FirstName , lastNamegroup by FirstName , lastName
March 3, 2008 at 9:46 am
I tried TonyJ's suggestion since it was easy enough for me.
select
FirstName
, lastName
, max(Case when ContractType = 'Fixed' then ContractType else '0' end)
+ ',' + max(Case when ContractType = 'TimeMaterial' then ContractType else '0' end)
+ ',' + max(Case when ContractType = 'CostType' then ContractType else '0' end)
+ ',' + max(Case when ContractType = 'Flexibly Priced' then ContractType else '0' end) as ContractType
from test1
where ContractType in('Fixed','TimeMaterial','CostType','Flexibly Priced')
group by FirstName , lastNamegroup by FirstName , lastName
Thanks TonyJ, It works but it doesn't work when u have this scenario
FirstName | LastName | ContractType
-------------------------------------------------
John | Smith | Fixed Price
Meaning u have only one ContractType and u don't have other Contract Types such as Cost Type, TimeMaterial, Flexibly Priced.
I get output
FirstName | LastName | ContractType
-------------------------------------------------
John | Smith | Fixed Price, , ,
I need this for SQL Reports, I don't care to generate any CSV files. I have field in my report where I have to display using columns in one row. Any help is appreciated.
Thanks
March 3, 2008 at 10:01 am
if you are looking at my sol. then
try this..
select FirstName,lastName, left(ContractType, len(ContractType)-1) as ContractType
from(
select
FirstName
, lastName
, isnull(max(Case when ContractType = 'Fixed' then ContractType else null end) + ',' ,'')
+ isnull(max(Case when ContractType = 'TimeMaterial' then ContractType else null end)+ ',' ,'')
+ isnull(max(Case when ContractType = 'CostType' then ContractType else null end) + ',' ,'')
+ isnull(max(Case when ContractType = 'Flexibly Priced' then ContractType else null end) + ',','') as ContractType
from test1
where ContractType in('Fixed','TimeMaterial','CostType','Flexibly Priced')
group by FirstName , lastName) t
March 5, 2008 at 11:51 am
if you are using Sql server 2005 you use Pivot operation to perform this
select firstName, lastName , left(ContractType ,len(ContractType)-1) as ContractType
from (
SELECT firstName, lastName , isnull([Fixed] + ',','')
+ isnull([TimeMaterial] + ',','')
+ isnull([CostType] + ',' ,'')
+ isnull([Flexibly Priced]+ ',','') as ContractType
FROM dbo.test1
PIVOT (max(ContractType) FOR ContractType IN
([Fixed],[TimeMaterial],[CostType],[Flexibly Priced])) as SalesPivot
) t
Thanks
March 5, 2008 at 11:59 am
Thanks TonyJ, It works but it doesn't work when u have this scenario
And, still, you haven't tried the solution that Ian posted which doesn't have that problem... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2008 at 12:03 pm
can you make it little more clear - in what scenario it will not work=?
Thanks
March 5, 2008 at 3:18 pm
This will work with multiple employees and contract types.
Chris
---
declare @firstName varchar(50),
@surName varchar(50),
@contractType varchar(500)
create table #results (firstName varchar(50),
surname varchar(50),
contractType varchar(500))
declare curRecs cursor local for
select distinct firstname, surname from #myTable
open curRecs
fetch next from curRecs into @firstName, @surName
while @@fetch_status = 0
begin
set @contractType = NULL
select @contractType = COALESCE(@contractType + ', ', '') + t.contractType
from #myTable t
where t.firstName = @firstName and t.surName = @surName
insert #results (firstName, surName, contractType)
values (@firstName, @surName, @contractType)
fetch next from curRecs into @firstName, @surName
end
select * from #results
drop table #results
March 6, 2008 at 4:18 am
You would use a cursor and while loop instead of a set based solution?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 9:27 am
Jeff,
Yes, I would consider using a cursor over the set based approach depending on the circumstances such as the size of the dataset and use for which I need the solution.
If you look at the solutions proposed ahead of mine, they all have hard-coded value tests in the select statement. That limits flexibility if the range of values every change. You are then left with trying to track down the bit of code and revise it.
Robert's post following your's actually makes use of the same technique as mine for creating the concatenated column. I do like his solution better with wrapping it in a function and then calling the function from the SELECT.
Chris
March 6, 2008 at 9:28 am
In ASA there is a LIST() aggregate function, so the query would be simple as this:
select FirstName , LastName , list(ContractType) from contrators
group by FirstName , LastName
MSSQL does not have it, unfortunately, but has something even more flexible - custom aggregates:
create function dbo.list_ContractType(@FirstName varchar(31), @LastName varchar(31)) returns varchar(1000)
begin
declare @result varchar(1000);
select @result=coalesce(@result+',','')+ContractType from contrators where firstname=@firstname and lastname=@lastname
return @result
end
go
select FirstName , LastName , list_ContractType(FirstName , LastName) from contrators
group by FirstName , LastName
you should have index for FirstName , LastName or better - normalize the table as it would improve the speed and storage efficiency.
March 6, 2008 at 1:49 pm
Chris Schanno (3/6/2008)
Jeff,Yes, I would consider using a cursor over the set based approach depending on the circumstances such as the size of the dataset and use for which I need the solution.
If you look at the solutions proposed ahead of mine, they all have hard-coded value tests in the select statement. That limits flexibility if the range of values every change. You are then left with trying to track down the bit of code and revise it.
Robert's post following your's actually makes use of the same technique as mine for creating the concatenated column. I do like his solution better with wrapping it in a function and then calling the function from the SELECT.
Chris
I must have missed something here; what was wrong with the XML solution Ian posted? It didn't have any hard-coded values. I thought it was very creative, and probably something I'd do in the future.
March 6, 2008 at 3:02 pm
Chris Schanno (3/6/2008)
Jeff,Yes, I would consider using a cursor over the set based approach depending on the circumstances such as the size of the dataset and use for which I need the solution.
The only time I even use a while loop is to break up a super large delete or update or to traverse databases. With the advent of VARCHAR(MAX), there's no need to use a While Loop to travers databases anymore. To actually process any type of columunar data in a RBAR fashion using a Cursor or While Loop, no matter what the reason, is pretty much the wrong thing to do. But, whatever... it's your code.
I agree... Bob's solution is the classic solution for this problem... notice... no cursor there, either. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 3:35 pm
Nothing wrong with XML solution. On the contrary, it's very good. I think I'll find plenty of good uses for it as I hate to create a function for each aggregate.
My solution is well known concept and works with older servers, XML solution is something new I'm glad I learned about. 😎
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply