Link to home
Start Free TrialLog in
Avatar of manelson05
manelson05Flag for United States of America

asked on

How do I change columnar (vertical) formatting to Horizontal

I have a large data set that I will need to use, it is currently within Excel.

I need the ability to place into a horizontal format to use in an UPDATE statement.

Currently data looks as such
                                                000019-09,
                                                000048-01,
                                                000080-01,

I need to be able to insert into SQL as follows
                                               000019-09, 000048-01,000080-01,


Thank you

Mark
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

What a coincidence.  Check out the article I just cranked out yesterday T-SQL:  Normalized data to a single comma delineated string and back for a SQL Server solution on how to go from normalized data to a scalar value that is multiple values separated by commas.

And yes I know the code is not easy..
Avatar of manelson05

ASKER

Jim, The data I am required to use for updates within the table were hand keyed descriptions that I need to update the fields with. I'm hopeful I can convert this date from excel format then simply copy into SSMS. You article is very helpful for some procedures that I normally run here.

Mark
>hand keyed descriptions that I need to update the fields with.
Not real sure what you mean by that, other than alarms are going off bigtime as users can manually enter Excel data in all sorts of ways that would eff up an automated ETL from Excel into anything normalized.

>I'm hopeful I can convert this date from excel format then simply copy into SSMS.
Especially dates, as ETL jobs will take the cosmetic rendering date and not the date itself, which is often not compatible with a database date datatype, and usually requires it be imported as a character and then 'scrubbed' to validate it's a date and then convert.
Sorry Jim not sure where dates came from.

These are simple Customized fields descriptors, they are text strings only.
I found TRANSPOSE in excel, it places in perfect format.
Correct, Excel Transpose goes from columns to rows and vice versa.  I assumed based on the commas that you were looking for a single string with values separated by commas answer.
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial