SQLTeam.com | Weblogs | Forums

I would like to merge 2 rows of related data in SQL

HI I would like to know if possible in SQL. I have data from a report in the app order connect. It's a list of all pharmacies we use about 11000 rows but the data comes into the report like this as 2 rows.

1 1Stop Pharmacy
2 rest of data its all one string like:
NCPDP ID: 33028765 459 Walt Mitman Rd Ronkoa NY 11747-2192 (631) 401-9494 (631) 420-0499 OrderConnect

So I want to concat on the second row, since the first row is just the pharmacy name. I wanted to know if the 2 rows can go on one row, so we would get columns for all the data like: I am holding this in a SQL Server express to easily load the excel.

Column 
PharmacyName    ID         Address                City      State   Phone1          Phone2
1StopPharmacy   33028765   459 Walt Mitman Rd      Ronkoa    NY     (631) 401-9494   (631) 420-0499

we don't need 'Orderconnect'

yes you can

idea number 1
SQL Server 2008 and below
image

idea number 2
lead lag functions .. SQL Server 2012 and above