Convert rows into columns

Question

Post reply

Convert rows into columns

Viewing 13 posts - 16 through 27 (of 27 total)

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

Shree-903371 SSCommitted Points: 1930 More actions · Answer 1

Shree-903371

SSCommitted

Points: 1930

October 13, 2008 at 9:12 am

#884213

No, the same one which you pasted i'm running.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 2

I figured out why, its because I’m working on SQL server 2005 but my database is in SQL server 2000. So that’s why its giving the error at XML as it works only in 2005.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 3

Shree-903371

SSCommitted

Points: 1930

October 13, 2008 at 9:42 am

#884241

Hi, do you think can we write without the XML?

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 4

You mentioned earlier that you had tried to create a cross tab report, but were unsuccessful.

Can you post that code here?

Also, are you allowed to use dynamic SQL from inside SQL Server? Meaning, can you create the string in SQL?

Shree-903371 SSCommitted Points: 1930 More actions · Answer 5

I created cross tab in crystal reports, so there was no code for it.

I didn’t understand the second one, but I think we can create string as long there is no dynamic SQL in the code.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Shree (10/13/2008)
Hi, do you think can we write without the XML?

Yep... see the following article...

http://www.sqlservercentral.com/articles/Test+Data/61572/

Also, the folks in charge of the database have made a bloody huge error in not allowing the power of Dynamic SQL. They've allowed their fear of injection attacks as supposedly bad performance to overpower one of the most useful tools there is. They really need to reconsider.

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

Shree-903371 SSCommitted Points: 1930 More actions · Answer 7

Hi, I saw that article and tried but its not working. Its giving me null values.

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Please post the code that you attempted that did not work. Second, please post sample data / table inserts according to the link in my signature. Third, please stop creating new threads on this issue. (We're up to what, 5 now?)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Shree-903371 SSCommitted Points: 1930 More actions · Answer 9

Sorry, actually I posted in the wrong forum before. I didn’t find the option to delete the topic. Anyway Madhivanan helped me by giving me this article link.

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Shree (10/14/2008)

Sorry, actually I posted in the wrong forum before. I didn’t find the option to delete the topic. Anyway Madhivanan helped me by giving me this article link.

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx%5B/quote%5D

That's a WHOLE lot different than your original request... you sure that's what you want? If so, see the following article for some major performance pitfalls to avoid for that method...

http://www.sqlservercentral.com/articles/Test+Data/61572/

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

Shree-903371 SSCommitted Points: 1930 More actions · Answer 11

As I couldn’t convert into separate columns, I will try in this way and I will talk to my user about it.

The same article was given my Amar, but it was taking lot of time to run. So I preferred the other way.

Thank you so much for your help.

I hope my User don’t mind if I get them in CSV format.

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 12

Shree,

You can use CASE command to display the rows into colums; for example;

CREATE TABLE [dbo].[fscity](

[state] [nchar](10) NULL,

[city] [nchar](10) NULL

) ON [PRIMARY]

I inserted couple of cities and used this command;

SELECT

CASE WHEN CITY LIKE 'B%' THEN CITY ELSE '' END as 'CITY1',

CASE WHEN CITY LIKE 'L%' THEN CITY ELSE '' END as 'CITY2',

CASE WHEN CITY LIKE 'H%' THEN CITY ELSE '' END as 'CITY3',

CASE WHEN CITY LIKE 'R%' THEN CITY ELSE '' END as 'CITY4',

STATE

FROM dbo.fscity

the result displays as;

City1 Cty2 City3 city4 State

leesburg VA

HERNDON VA

RESTON VA

BOE VA

VA

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 13

Try this