Changing row format

Question

Post reply

Changing row format

fred-642361

Old Hand

Points: 377
More actions
June 5, 2008 at 9:09 am

#191752

I have a dilemma, it may be simple but the answer alludes me.
I have a table that I imported from an excel spread sheet with vendor names and attendee names for those vendors, but the table lists the vendor and all the names in the same row, what I would like to do is have the vendors name listed for each attendee in a vertical format.
Current
Vendor Name Attendee Name
VENDOR1 name1 name2 name3 name4 name5
Desired
Vendor Name Attendee Name
VENDOR1 name1
VENDOR1 name2
VENDOR1 name3
VENDOR1 name4
VENDOR1 name5
Any help would be appreciated

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI,

Have a read in the articles sections for Jeffs create articles on Tally/Number tables 🙂

If you have trouble gives us a should

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Sorry here is the link:

http://www.sqlservercentral.com/articles/TSQL/62867/

Look at the stepping through strings sextion.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

here is answer

SELECT [Vendor Name],SUBSTRING([Attendee Name]+' ', number,

CHARINDEX(' ', [Attendee Name]+' ', number) - number)

FROM MyTable [My]

INNER JOIN Tally [T]

ON number < = LEN([Attendee Name])

AND SUBSTRING(' ' + [Attendee Name],

number, 1) = ' '

ORDER BY [Vendor Name]

But I highly recommend reading those articles of Jeffs as they are very very usefull

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

karthik M One Orange Chip Points: 29139 More actions · Answer 4

As Christoper suggested ,read that article.

You can do it without using any loops.

karthik

fred-642361 Old Hand Points: 377 More actions · Answer 5

Christopher,

Thanks for the quick response. I don’t think I was clear on the current example, the way I have it displayed you would think that the attendee names were merged under the column name Attendee Name, but actually they are separate columns, see correction. With this new information taken in to account would your solution still be valid.

Current

Vendor Name name1 name2 name3 name4 name5

VENDOR1 name name name name name

Desired

Vendor Name Attendee Name

VENDOR1 name

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

Yes... same solution... just export the data as tab delimited and do the split on the tab.

--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: 1004340 More actions · Answer 7

Another way is to import the spreadsheet into a table as is... then, do something like this to normalize it...

SELECT Vendor,Name1 AS Attendee

FROM sometable

UNION ALL

SELECT Vendor,Name2 AS Attendee

FROM sometable

UNION ALL

SELECT Vendor,Name3 AS Attendee

FROM sometable

UNION ALL

SELECT Vendor,Name4 AS Attendee

FROM sometable

UNION ALL

SELECT Vendor,Name5 AS Attendee

FROM sometable

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

Hi again,

If I'm not wrong you could also use the UNPIVOT funtion.

Check it out on BOL

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

HEre is an example:

DECLARE @MyTable TABLE

(Vendor VARCHAR(10),

Name1 VARCHAR(10),

Name2 VARCHAR(10),

Name3 VARCHAR(10),

Name4 VARCHAR(10),

Name5 VARCHAR(10))

INSERT INTO @MyTable

SELECT 'Vendor1','NameA','NameB','NameC','NameD','NameE'

SELECT DISTINCT

Vendor

,[Name]

FROM

(

SELECT Vendor ,Name1,Name2,Name3,Name4,Name5

FROM @MyTable

) pvt

UNPIVOT ([Name] FOR Header IN ([Name1],[Name2],[Name3],[Name4],[Name5])) unpvt

Just remember that if the names in the columns are the same you will only get distinct Vendor to Name rows.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 10

I am a huge fan of the fact that SQL 2005 introduced pivot and unpivot kind of functionality, but a little bit disappointed that it is only slightly better than case statements.

I still need to go and hardcode the column names, unless I start using some seriously mean dynamic TSQL (which I did resort to, but it was a mission and it used that RBAR swearword...).

Excel and Access still rule on Pivots, in Excel you simply drag the column to the top, and in Access you have crosstab queries.

Not to say its all bad, its still an improvement on monster case statements...

Anyone got any feedback on the pivot functionality in Katmai? Is it improved or more of the same?

~PD

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

You didn't listen if you ended up using RBAR and hardcoded names... you didn't read the articles that were suggested well enough.

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

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 12

Hey Jeff,

Any helpful links? I googled till I was blue as I didnt want to use any form of a cursor but couldnt seem to get away from it.

Would be highly appreciated

Phil

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 13

Actually, it was code that I wrote quite a while ago, and I huuuuugely stand under correction.

I am sooooo sorry! I just went and revisited that code, and I was able to add some dynamics. It is true that I googled till I was blue, but did come up with something that didnt require nasty cursors and hardcoded fieldnames. Humblest apologies....

if exists(select * from sysobjects where name = 'Values_Analysis')

begin

drop table dbo.Values_Analysis

end

exec ('select piv.*,

fg.Field1, fg.Field2, fg.Field3,

into dbo.Values_Analysis

from (

SELECT p.KeyField, ' + @sellist

+ '

FROM db1.dbo.Aggregations

PIVOT

(

SUM(TOTAL)

FOR AGG_TYPE

IN ('+ @colList +')

) p

) piv

left joindb1.dbo.table2 fg

onpiv.KeyField = fg.KeyField

)

Once more, humblest apologies

~PD

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

Post what you did for the problem... attach the spreadsheet you used if nothing private is in it.. let's give 'er a whirl. 😀

Any links I would be using at this point, would only be my own... 😉

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