simple one table query

Question

simple one table query

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 1

Performance Guard (Shehap) (10/15/2011)
If huge data entity for table1 , please let me know to guide more about the best performance options

That seems quite contrary to what you said after that...

To clarify more ....Thinking of all performance terms is the best way to keep your production servers healthy all the time....

If what you said immediately above is true (and it is), why would you post code that [font="Arial Black"]could [/font]become a performance problem as scale increases?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

erhanduman (10/15/2011)
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

Erhan,

The reason why I asked you to post what you've tried with Pivots and Crosstabs is because I believe that's the right track for this problem. Please post the code you've tried so I can teach another person "how to fish" on this problem (and I'm assuming that you actually want the 4 column output instead of just a 2 column output as some have suggested... if the two column solution is ok, please let me know).

Also, are you guaranteed to have a maximum of only 3 items per instance of F1?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

To erhanduman ..the OP

I hope that we havent hijacked your original post to the extent that you are unable to find some ideas.....;-)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 4

chandan_jha18 (10/15/2011)
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

NO! Temp tables are not necessarily bad and, NO, they shouldn't necessarily be avoided (although I don't believe they necessary at all for this particular problem). As has been said so many times already, "It Depends". Sometimes "all-in-one" queries that use CTE's are lightning fast and efficient. Sometimes, they need to be split up into multiple SQL statements possible even using Temp Tables to store interim results to get the performance you need. I'll say it again... "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

Here's my solution for the original problem. Keep in mind that I'm assuming from the original description of the problem that there are no more than three F2s for each F1. If there can be more, then we'll need a little dynamic SQL to account for that unknown.

Please substitute the correct table name in the following code.

WITH

cteEnumerateFruit AS

(

SELECT FruitNumber = ROW_NUMBER() OVER (PARTITION BY F1 ORDER BY F1),

F1,

F2

FROM #TestTable

)

SELECT F1,

[F2.1] = MAX(CASE WHEN FruitNumber = 1 THEN F2 ELSE '' END),

[F2.2] = MAX(CASE WHEN FruitNumber = 2 THEN F2 ELSE '' END),

[F2.3] = MAX(CASE WHEN FruitNumber = 3 THEN F2 ELSE '' END)

FROM cteEnumerateFruit

GROUP BY F1

;

If anyone would like to play with alternate solutions, here's the test data I built for this problem... do note the index that I also create. The code builds approximately 1 million rows of test data consisting of 386,000 unique F1s each having 1 to 3 entries for F2. On my 9 year old desktop box, the code only takes about 64 seconds to execute...

--===== Conditionally drop the temp tables to make reruns easier in SSMS.

IF OBJECT_ID('tempdb..#Fruit' ,'U') IS NOT NULL DROP TABLE #Fruit;

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;

GO

--===== Create a temp table to hold fruit names so we can create a lot

-- of test data from it later.

SELECT FruitID = IDENTITY(INT,1,1),

FruitName = CAST(fruit.FruitName AS VARCHAR(20))

INTO #Fruit

FROM (

SELECT 'apple' UNION ALL

SELECT 'banana' UNION ALL

SELECT 'grape' UNION ALL

SELECT 'melon' UNION ALL

SELECT 'orange' UNION ALL

SELECT 'plum' UNION ALL

SELECT 'strawberry'

) fruit (FruitName)

;

--===== Build and populate the test table on the fly.

WITH

cteBuildF1 AS

( --=== This simply builds an ascending integer for values of F1

SELECT TOP 386000

F1 = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

) --=== This assigns up to 3 fruits to each value of F1

-- and stores them in a test table

SELECT DISTINCT

fid.F1,

F2 = fruit.FruitName

INTO #TestTable

FROM cteBuildF1 bf1

CROSS APPLY

(

SELECT F1, ABS(CHECKSUM(NEWID()))%7+1 UNION ALL

SELECT F1, ABS(CHECKSUM(NEWID()))%7+1

) fid (F1, FruitID)

INNER JOIN #Fruit fruit

ON fid.FruitID = fruit.FruitID

;

--===== I don't know what other indexes may appear on the original table

-- but the following index helps performance of most code quite a bit.

CREATE INDEX IX_#TestTable_F1 ON #TestTable (F1) INCLUDE (F2)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)