A strange T-SQL challenge

Question

A strange T-SQL challenge

Viewing 12 posts - 16 through 26 (of 26 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

1. Why does the pivot table need the aggregate function MIN in order to work?
2. Why did you need the column Row_number?

1. PIVOT needs an aggregate function you could have use MAX() or even AVG() too because it will really be applied to only one row.

2. We need to identify 4 columns values that will be repeated "exactly" row-by-row with 4 possible values which in the source table you don't have. To see it in a clearer manner just run the Src query by it self and inspect the results.

HTH

* Noel

meichner SSCertifiable Points: 5563 More actions · Answer 2

noeld (11/6/2008)
1. Why does the pivot table need the aggregate function MIN in order to work?
2. Why did you need the column Row_number?
1. PIVOT needs an aggregate function you could have use MAX() or even AVG() too because it will really be applied to only one row.
2. We need to identify 4 columns values that will be repeated "exactly" row-by-row with 4 possible values which in the source table you don't have. To see it in a clearer manner just run the Src query by it self and inspect the results.
HTH

I want to thank you and everyone else who pitched in to help me out.

Thanks again

noeld SSC Guru Points: 96590 More actions · Answer 3

noeld

SSC Guru

Points: 96590

November 6, 2008 at 1:12 pm

#895183

Happy to help

* Noel

lijusankar Grasshopper Points: 11 More actions · Answer 4

DECLARE @table TABLE (Name VARCHAR(10), Mark1 INT, Mark2 INT,Mark3 INT,Mark4 INT)

INSERT INTO @table (Name, mark1, mark2, Mark3, Mark4) SELECT 'Liju',1,2,3,4

INSERT INTO @table (Name, mark1, mark2, Mark3, Mark4) SELECT 'Siju',10,20,30,40

INSERT INTO @table (Name, mark1, mark2, Mark3, Mark4) SELECT 'Biju',11,12,13,14

INSERT INTO @table (Name, mark1, mark2, Mark3, Mark4) SELECT 'Viju',11,21,31,41

SELECT * FROM @table

DECLARE @Result AS NVARCHAR(1000)

SET @Result =''

SELECT @Result = @Result + Name +','+ CAST(mark1 AS NVARCHAR(10)) + ',' + CAST(mark2 AS NVARCHAR(10)) + ','

FROM @table

WHERE Name = 'Liju'

SELECT @Result

drew.allen SSC Guru Points: 76985 More actions · Answer 5

First, this thread is almost three years old.

Second, the OP has a table that is normalized and asks how to denormalize it. Your sample data starts out denormalized. If you're going to resurrect old threads, make absolutely sure that you understand the question that was being asked.

lijusankar (5/24/2011)
DECLARE @table TABLE (Name VARCHAR(10), Mark1 INT, Mark2 INT,Mark3 INT,Mark4 INT)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

cstevens 101 Newbie Points: 9 More actions · Answer 6

I'm new to SQL so this is more a question than a statement, but couldn't you select the data into a temp table, then count the records to set a variable for a while loop?

DECLARE @i int

DECLARE @OrderNo varchar(100)

DECLARE @qty1 varchar(100)

DECLARE @qty2 varchar(100)

DECLARE @qty3 varchar(100)

DECLARE @qty4 varchar(100)

--obviously change the vars to whatever type you like

SELECT INTO ##tempTable1

OrderNo, Qty

FROM whateverTable

Where OrderNo = '00001'

--returns 3 records

while @i < (Select Count(*) from ##tempTable1)

begin

--build a cursor - for each row in tempTable1

SET @OrderNo = (SELECT OrderNo From ##tempTable1)

SET (@qty + @i) = (Select Qty From ##tempTable1)

insert into ##tempTable2

@OrderNo,@qty1,@qty2,@qty3

--destory the cursor

Am I way off base on this? How whould this effect performance

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

We don't like cursors much here 🙂 Can you tell us what you are trying to achieve & we'll almost certainly find a faster way to do it.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

cstevens 101 Newbie Points: 9 More actions · Answer 8

Sorry, I was posting a response to another person, but I posted to the wrong location.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 9

Like other people suggest i would approach this using a pivot table, why does you boss object to a using pivot?

***The first step is always the hardest *******

drew.allen SSC Guru Points: 76985 More actions · Answer 10

SGT_squeequal (3/20/2012)
Like other people suggest i would approach this using a pivot table, why does you boss object to a using pivot?

You do realize that that comment was made THREE YEARS AGO? I suspect that the situation has changed in the intervening three years.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 11

no need to shout, no i do not look at the dates in the post, it appeard as a topic entered in the last 24 hours therefore are current posts

***The first step is always the hardest *******

cstevens 101 Newbie Points: 9 More actions · Answer 12

@ SGT_squeequal - Thank you for responding to my post in a helpful and informative manner.

@ Drew Allen - Normally, I don't respond to people that are being rude, but I've recently decided to change my stance on this policy.

Honestly, who cares if the post is THREE YEARS OLD? If someone has an answer, it would be nice to see it posted, and there is the possibility that someone else is having, or will have the same or an similar issue in the future. Some of us new guys like to search the posts for an answer before we ask a question, and read these posts for general knowledge. CHILL OUT!!!