query to modify

Question

query to modify

pmadhavapeddi22

SSCertifiable

Points: 5306
More actions
October 13, 2009 at 10:34 am

#156313

Hi
I have data like the below
channel line_of_bus prod_nm
all C1 last year p&c
all D1 busi module
all d1 bus term
IA c1 exp cum total
IA D1 busi module
I need to disply like below
channel line_of_bus
all C1
all last year p&c
all D1
all busi module
all d1
all bus term
IA c1
IA exp cum total
IA D1
IA busi module
Is it possible? Please help me , its little urgent
Thanks

Viewing 15 posts - 1 through 15 (of 17 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

If this is urgent then you may getter a quicker response if you post your create table statement, and some sample data in the form of Insert statements..

LutzM SSC Guru Points: 107049 More actions · Answer 2

To post sample data please follow the first link in my signature.

Another option would be to check BOL for UNPIVOT function ...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

This is definitely NOT the place to ask urgent questions. I've got a solution for you, but first I'd like to know why this is urgent.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

LutzM SSC Guru Points: 107049 More actions · Answer 4

Bob Hovious 24601 (10/13/2009)
I've got a solution for you, but first I'd like to know why this is urgent.

Now the OP know's that there are (at least) two people with a solution ready to post just waiting for a more detailed reply... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

steveb. SSC-Forever Points: 46733 More actions · Answer 5

steveb.

SSC-Forever

Points: 46733

October 14, 2009 at 10:44 am

#1065842

Well i guess it wasn't really urgent after all..

LutzM SSC Guru Points: 107049 More actions · Answer 6

steveb. (10/14/2009)
Well i guess it wasn't really urgent after all..

Or the UNPIVOT hint pointed him in the right direction (at least one of the directions leading to the expected result....)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 7

Or he got run over by a bus... :crying:

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

I don't know folks... don't kill me for this but he is a newbie, he asked very politely, it wasn't one of those "URGENT, URGENT!!!!" requests that we frequently see, and it sure doesn't look like homework or and interview question.

pmadhavapeddi22,

Some of us have absolutely been beat to death by folks with "URGENT" problems that turn out to be someone with a homework or interview problem so please pardon the response you've gotten. I will say that you can really get a quick and fully tested response in the form of working code if you format your data examples in a readily consumable format. Please see the first link in my signature below for how to do that. I've also included an example in the solution code below. Please read the comments...

--===== This just creates a test table and isn't part of the solution.

-- Most folks want to see the data you post in this form because

-- they like to test their solutions before posting them. See the

-- first link in my signature below for an easy way to do this.

-- It'll also help get your question answered much quicker.

IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable;

CREATE TABLE #TestTable

(

channel VARCHAR(3),

line_of_bus VARCHAR(2),

prod_nm VARCHAR(20)

);

INSERT INTO #TestTable

(channel,line_of_bus,prod_nm)

SELECT 'all', 'C1', 'last year p&c' UNION ALL

SELECT 'all', 'D1', 'busi module' UNION ALL

SELECT 'all', 'd1', 'bus term' UNION ALL

SELECT 'IA' , 'c1', 'exp cum total' UNION ALL

SELECT 'IA' , 'D1', 'busi module';

GO

--===== This is the solution I'd use in 2k5.

-- This works but won't allow the use of a table alias on "Channel".

-- It's also the fastest method that you can use in SQL Server 2005.

WITH

cteFixDataType AS

(

SELECT ROW_NUMBER() OVER (ORDER BY Channel,line_of_bus,prod_nm) AS RowNum,

Channel,

CAST(line_of_bus AS VARCHAR(20)) AS line_of_bus,

prod_nm

FROM #TestTable

)

SELECT Channel, --can't use fdt table alias on this or BOOM!

up.ColumnValue AS line_of_bus

FROM cteFixDataType fdt

UNPIVOT (ColumnValue FOR ColName IN (fdt.line_of_bus, fdt.prod_nm)) AS up;

The ROW_NUMBER() OVER thingy in the CTE provides the correct order even if dupes are present.

--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)
Intro to Tally Tables and Functions

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 9

Jeff,

I have a couple of questions:

(1) Who are you, and (2) what have you done with Jeff Moden?

Alternatively:

(1) What kind of tranquilizers are you taking?

(2) Do you have enough to share?

In our defense, despite our questioning the "urgency", we did offer solutions awaiting only his response. I even formatted the sample data for him.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Supriya.S Ten Centuries Points: 1131 More actions · Answer 10

Jeff Moden (10/16/2009)
I don't know folks... don't kill me for this but he is a newbie, he asked very politely, it wasn't one of those "URGENT, URGENT!!!!" requests that we frequently see, and it sure doesn't look like homework or and interview question.

Thank you so much Jeff. You put it way better than what I wanted to say in "The Thread".

God bless you!

Regards,

Supriya

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Bob Hovious 24601 (10/16/2009)
Jeff,
I have a couple of questions:
(1) Who are you, and (2) what have you done with Jeff Moden?
Alternatively:
(1) What kind of tranquilizers are you taking?
(2) Do you have enough to share?
In our defense, despite our questioning the "urgency", we did offer solutions awaiting only his response. I even formatted the sample data for him.

It's still me and the tranqs came from "The Thread". There was one post that identified two folks having "urgent" problems... folks helped one and not the other despite the very subtle differences (the one above actually being the least offensive of the two to me) and it just got to me. I've been between jobs for a couple of weeks (I start a new one on Monday) and I haven't been drinking as much coffee as I should. 😛

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

s ss (10/16/2009)
Thank you so much Jeff. You put it way better than what I wanted to say in "The Thread".
God bless you!
Regards,
Supriya

Thanks Supriya... this one bugged me. Reminded me of why I started helping on this forum... pass my good fortune forward.

--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)
Intro to Tally Tables and Functions

LutzM SSC Guru Points: 107049 More actions · Answer 13

I just checked this thread again and figured that OP's last login was on 10/14/2009 9:30:22 AM (assuming, he's not using multiple nicknames).

So he missed all the solutions provided...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Lesson learned on my part...

--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)
Intro to Tally Tables and Functions