October 15, 2011 at 9:02 am
Hello;
I have table;
These values are
F1F2
621apple
621orange
622banana
622grape
622strawberry
623apple
623plum
623melon
624apple
624strawberry
I want convert this table.
F1F2.2F2.2F2.3
621appleorange
622bananagrapestrawberry
623appleplummelon
624applestrawberry
I'm tried with join and pivot table methods. But I can't do it. Is there any suggestion
Thanks
Erhan
October 15, 2011 at 9:43 am
I am going to assume this is "homework"...this question or variations of it, appear on SSC regularly.
so...food for thought...hope this gives you some ideas.
-- create some data
with produce (id,fruit, varieties)
as (
SELECT 101,'Apple', '3' UNION ALL
SELECT 101,'Banana', '2' UNION ALL
SELECT 102,'Orange', '1' UNION ALL
SELECT 103,'Melon' ,'2' UNION ALL
SELECT 103,'Grape' ,'1' UNION ALL
SELECT 104,'Apple' ,'1' UNION ALL
SELECT 105,'Banana' ,'1' UNION ALL
SELECT 105,'Kiwi' ,'1' UNION ALL
SELECT 105,'Tangerine' ,'1' UNION ALL
SELECT 106,'Mango' ,'3' UNION ALL
SELECT 106,'Melon' ,'2'
)
--query as follows
SELECT id,
Stuff((SELECT ',' + fruit + ' (' + varieties + ') : '
FROM produce p2
WHERE p1.id = p2.id
ORDER BY p2.fruit --- sort by Fruit name
FOR XML PATH('')), 1, 1, ' ')
FROM produce p1
GROUP BY id
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 15, 2011 at 12:06 pm
erhanduman (10/15/2011)
I'm tried with join and pivot table methods. But I can't do it. Is there any suggestionThanks
Erhan
Cool... show us the code you tried and we'll show where you may have gone wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2011 at 12:11 pm
You could proceed it using the below query :
create table #temptable (F1 int, [F2.1] varchar (20), [F2.2] varchar (20), [F2.3] varchar (20))
insert into #temptable (F1 )
select Distinct F1 from table1
update #temptable SET [F2.1]=(select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1) S inner join #temptable ON s.F1 =#temptable.F1)
update #temptable SET [F2.2]= (select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1 ) S inner join #temptable ON s.F1 =#temptable.F1 and s.F2 <>#temptable.[F2.1])
update #temptable SET [F2.3]= (select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1 ) S inner join #temptable ON s.F1 =#temptable.F1 and s.F2 <>#temptable.[F2.1] and s.F2<>#temptable.[F2.2])
select * from #temptable
drop table #temptable
If huge data entity for table1 , please let me know to guide more about the best performance options
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 15, 2011 at 12:22 pm
A temp table and 3 updates for what can be done in a single query? Not exactly the best option around for performance...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2011 at 12:35 pm
Performace Guard (Shehap) (10/15/2011)
You could proceed it using the below query :create table #temptable (F1 int, [F2.1] varchar (20), [F2.2] varchar (20), [F2.3] varchar (20))
insert into #temptable (F1 )
select Distinct F1 from table1
update #temptable SET [F2.1]=(select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1) S inner join #temptable ON s.F1 =#temptable.F1)
update #temptable SET [F2.2]= (select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1 ) S inner join #temptable ON s.F1 =#temptable.F1 and s.F2 <>#temptable.[F2.1])
update #temptable SET [F2.3]= (select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1 ) S inner join #temptable ON s.F1 =#temptable.F1 and s.F2 <>#temptable.[F2.1] and s.F2<>#temptable.[F2.2])
select * from #temptable
drop table #temptable
If huge data entity for table1 , please let me know to guide more about the best performance options
Hi Shehap.....
For legibility...may I suggest that you post future code in an easier to read format?
...its easier to read and understand the thought process.
for example:
CREATE TABLE #temptable
(
F1 INT,
[F2.1] VARCHAR (20),
[F2.2] VARCHAR (20),
[F2.3] VARCHAR (20)
)
INSERT INTO #temptable
(F1)
SELECT DISTINCT F1
FROM table1
UPDATE #temptable
SET [F2.1] = (SELECT TOP 1 S.F2
FROM (SELECT F2,
f1
FROM TABLE1) S
INNER JOIN #temptable
ON s.F1 = #temptable.F1)
UPDATE #temptable
SET [F2.2] = (SELECT TOP 1 S.F2
FROM (SELECT F2,
f1
FROM TABLE1) S
INNER JOIN #temptable
ON s.F1 = #temptable.F1
AND s.F2 <># temptable.[F2.1])
UPDATE #temptable
SET [F2.3] = (SELECT TOP 1 S.F2
FROM (SELECT F2,
f1
FROM TABLE1) S
INNER JOIN #temptable
ON s.F1 = #temptable.F1
AND s.F2 <># temptable.[F2.1]
AND s.F2 <># temptable.[F2.2])
SELECT *
FROM #temptable
DROP TABLE #temptable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 15, 2011 at 12:54 pm
Thanks for hints
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 15, 2011 at 1:32 pm
GilaMonster (10/15/2011)
A temp table and 3 updates for what can be done in a single query? Not exactly the best option around for performance...
Hi Monster,
Are temp tables bad and should be avoided? Sometimes when we use CTE or other super duper techniques, i think my management studio shows 'worktables' when i use io statistics statements. So sounds as if they are created internally while execution. Please explain what you meant here so that I can get your point.
Regards
Chandan
October 15, 2011 at 2:07 pm
From performance view point :
1.Create temp table commands are always better than declare temp table commands where temp clustered + non clustered index could be there as well
2.Create temp table are always better than TVF particularly more if they are used within inner join
3.Temp able are useful if to insert so specific filtered data entity not huge data entity size
So our case here matches up well with the third point
Therefore , I have stated “If huge data entity for table1 , please let me know to guide more about the best performance options”
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 15, 2011 at 6:07 pm
chandan_jha18 (10/15/2011)
Are temp tables bad and should be avoided?
No, there are cases where they are very useful. However in the example I commented on, a temp table is totally unnecessary and a waste of resources as that can be done in a single query with no temp tables and no correlated subqueries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2011 at 10:16 pm
Performace Guard (Shehap) (10/15/2011)
From performance view point :1.Create temp table commands are always better than declare temp table commands where temp clustered + non clustered index could be there as well
2.Create temp table are always better than TVF particularly more if they are used within inner join
3.Temp able are useful if to insert so specific filtered data entity not huge data entity size
So our case here matches up well with the third point
Therefore , I have stated “If huge data entity for table1 , please let me know to guide more about the best performance options”
I'd be real careful about using the word "always". For example, although I don't use them for other reasons, it IS possible to create clustered and non-clustered indexes on "declare temp table commands" (I assume you mean "Table Variables" by that) at the time of creation through the use of constraints. It's also possible to make iTVFs that will run faster than a Temp Table type of query.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2011 at 4:43 am
Jeff ,
To clarify more ....Thinking of all performance terms is the best way to keep your production servers healthy all the time....
So if you intend to use TVF , you will get much table scan resulted by TVF where no index could be there at all >>>leading to More CPU overload particularly more if used within Joins since you will get more Table scans resulted by TVFs
This consideration above has participated much in saving a lot of production servers from catastrophic points of performance.
But using Temp table is also an article and needed to be judged in use like:
1.If huge data entity to be inserted within temp table >>> then it is a bad practice due to much I/O cost there
2.If small data entity like our current case , it is so cool.
If any more details about performance terms + considerations …Please let me know
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 16, 2011 at 7:58 am
Performace Guard (Shehap) (10/15/2011)
You could proceed it using the below query :create table #temptable (F1 int, [F2.1] varchar (20), [F2.2] varchar (20), [F2.3] varchar (20))
insert into #temptable (F1 )
select Distinct F1 from table1
update #temptable SET [F2.1]=(select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1) S inner join #temptable ON s.F1 =#temptable.F1)
update #temptable SET [F2.2]= (select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1 ) S inner join #temptable ON s.F1 =#temptable.F1 and s.F2 <>#temptable.[F2.1])
update #temptable SET [F2.3]= (select TOP 1 S.F2 FROM (SELECT F2 , f1 from TABLE1 ) S inner join #temptable ON s.F1 =#temptable.F1 and s.F2 <>#temptable.[F2.1] and s.F2<>#temptable.[F2.2])
select * from #temptable
drop table #temptable
If huge data entity for table1 , please let me know to guide more about the best performance options
Here is a solution for a million row test table....no requirement for temp tables
USE [tempdb] --- a safe place
GO
--== condtionally drop table for retesting in SSMS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLE1]') AND type in (N'U'))
DROP TABLE [dbo].[TABLE1]
GO
--== create some sample data 1M rows
SELECT TOP 1000000
F1 = CAST(ABS(CHECKSUM(NEWID())) % 100000 + 1 as int),
F2 = CHAR(Abs(Checksum(Newid())) % 10 + 65)+ CHAR(Abs(Checksum(Newid())) % 10 + 65)
into TABLE1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
--select * from t3 order by F1,f2
CREATE NONCLUSTERED INDEX [IX_NC_TABLE1]
ON [dbo].[TABLE1] ( [F1] ASC, [F2] ASC )
--== run query
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT f1,
Stuff((SELECT ', ' + f2
FROM TABLE1 p2
WHERE p1.f1 = p2.f1
ORDER BY p2.f2
FOR XML PATH('')), 1, 1, ' ')
FROM TABLE1 p1
GROUP BY f1
ORDER BY f1
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 16, 2011 at 8:57 am
Performace Guard (Shehap) (10/16/2011)
So if you intend to use TVF , you will get much table scan resulted by TVF where no index could be there at all >>>leading to More CPU overload particularly more if used within Joins since you will get more Table scans resulted by TVFs
Not necessary. Depends what you're doing and what kind of TVF you're using.
Inline table valued functions can be extremely efficient as they're inlined into the query during parsing. They're no more and no less than parameterised views.
Multi-statment table-valued user-defined functions are a different beast and they're the ones that are typically slow, thought it's often because SQL chooses inappropriate index seeks and key lookups rather than table scans because of the lack of statistics.
Unless the multi-statment table-valued user-defined function is returning thousands and thousands of rows (generally not a good practice anyway), the scan of the function's table variable won't be the biggest problem with the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2011 at 10:05 am
Performance Guard (Shehap) (10/16/2011)
Jeff ,To clarify more ....Thinking of all performance terms is the best way to keep your production servers healthy all the time....
You're definitely preaching to the choir there, Shehap. I don't believe there's a person on this forum that would disagree with that although I wouldn't use the absolutes you did because there are other ways to achieve great performance without actually thinking about writing code for the express purpose of getting great performance.
One fine example is the person who doesn't know how to do something all in one query and, as a result, divides a given problem into a couple or several SQL Statements just to make the problem easier. Frequently, using such a "Divide'n'Conquer" method can lean to huge gains in performance... quite by accident. 😉
So if you intend to use TVF , you [font="Arial Black"]will [/font]get much table scan resulted by TVF where no index could be there at all >>>leading to More CPU overload particularly more if used within Joins since you will get more Table scans resulted by TVFs
There you go again with the "absolutes"... if you said "can" instead of "will", I'd agree and say that, yes, you have to be careful about such things. But, as Gail and JLS have already pointed out, "It Depends" very much on what you're doing and how you're doing it in a TVF. Yes, I agree that mTVFs (Multi-line Table Valued Functions) [font="Arial Black"]can [/font]be as bad or worse that Scalar UDFs but and again, as Gail pointed out, iTVFs [font="Arial Black"]can [/font]be an incredibly important tool to achieving great performance because they work exactly as if they were a parameterized view. Yes, done improperly or in certain instances, they're just like any other piece of code... they [font="Arial Black"]can [/font] also create a severe performance problem.
This consideration above has participated much in saving a lot of production servers from catastrophic points of performance.
Agreed. But I've also seen such considerations cause catastrophic points of performance. "It Depends".
But using Temp table [font="Arial Black"]is[/font] also an article and needed to be judged in use like:
1.If huge data entity to be inserted within temp table >>> then it [font="Arial Black"]is[/font] a bad practice due to much I/O cost there
2.If small data entity like our current case , it is so cool.
Once again, you're using the absolute of "is" instead of "can". Again, I don't care to use Table Variables for reasons not having anything to do with performance, but I do use Temp Tables quite successfully to get tremendous performance improvements [font="Arial Black"]in many cases[/font]. [font="Arial Black"]In many other cases[/font], there are better ways. "It Depends".
If any more details about performance terms + considerations …Please let me know
Yes... my recommendation concerning terms of performance is to stop using absolute terms such as "will" and "is" because there [font="Arial Black"]can [/font]be frequent and varied exceptions to everything.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply