Query Help

Question

Query Help

DBA12345

Right there with Babe

Points: 742
More actions
August 27, 2013 at 6:39 pm

#282554

Hi
I have a requirement like below. I have to give the flavours to the application.We have thousands of records to update.
Please help me.
Actual table
APP|||COLOUR|||FLAVOUR
ap1|||GREEEN
ap1|||YELLOW
ap1|||YELLOW
ap1|||GREEEN
ap2|||YELLOw
ap2|||YELLOW
ap2|||YELLOW
ap3|||GREEEN
ap3|||YELLOW
ap3|||RED
ap3|||GREEEN
ap4|||GREEN
ap4|||GREEN
ap4|||GREEN
Output would look like below
APP|||COLOUR|||FLAVOUR
ap1|||GREEEN|||YELLOWA
ap1|||YELLOW|||YELLOWA
ap1|||YELLOW|||YELLOWA
ap1|||GREEEN|||YELLOWA
ap2|||YELLOW|||YELLOWB
ap2|||YELLOW|||YELLOWB
ap2|||YELLOW|||YELLOWB
ap3|||GREEEN|||RED
ap3|||YELLOW|||RED
ap3|||RED |||RED
ap3|||GREEEN|||RED
ap4|||GREEN|||GREENA
ap4|||GREEN|||GREENA
ap4|||GREEN|||GREENA
let me know if you need additional information
Thanks
Aswin

Viewing 5 posts - 1 through 4 (of 4 total)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

On basis of what criteria you are assigning flavours to the output. please provide more details on it..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You haven't explained any rules for the assignment of 'flavour'. Will a guess be sufficient?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

DBA12345 (8/27/2013)
Hi
let me know if you need additional information

'Nuff said. 🙂

--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)