Merging Columns to one line to export to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merging Columns to one line to export to Excel

Author

Topic

cordeparker
Starting Member

5 Posts

Posted - 2012-07-16 : 13:25:08

I've been having an issue with this and can't figure it out. This is the SQL select statement

SELECT ReorderDirections.FacID, Reorders.PatID,
Reorders.LastRxNo, Patients.PatLName,
Patients.PatFName, Reorders.DrugLabelName, ReorderDirections.Sig, ReordersAdminTimes.AdminTime
FROM Pat.dbo.Patients Patients, Pat.dbo.ReorderDirections ReorderDirections,
Pat.dbo.Reorders Reorders, Pat.dbo.ReordersAdminTimes ReordersAdminTimes
WHERE ReorderDirections.FacID = Patients.FacID AND ReorderDirections.PatID = Patients.PatID
AND Reorders.FacID = Patients.FacID AND Reorders.FacID = ReorderDirections.FacID
AND Reorders.PatID = Patients.PatID AND Reorders.PatID = ReorderDirections.PatID
AND Reorders.RoNo = ReorderDirections.RoNo AND ReordersAdminTimes.FacID = Patients.FacID
AND ReordersAdminTimes.FacID = ReorderDirections.FacID AND ReordersAdminTimes.FacID = Reorders.FacID
AND ReordersAdminTimes.PatID = Patients.PatID AND ReordersAdminTimes.PatID = ReorderDirections.PatID
AND ReordersAdminTimes.PatID = Reorders.PatID AND ReordersAdminTimes.RoNo = ReorderDirections.RoNo
AND ReordersAdminTimes.RoNo = Reorders.RoNo AND ReordersAdminTimes.Split = ReorderDirections.Split
AND ((Patients.PatStatus='A') AND (Reorders.CutoffDt Is Null))

It come out into excel like this

but I want the ReordersAdminTimes.AdminTime to be on the same line where the Reorders.LastRxNo is the same. So it will look like this.

Any ideas? I tried posting on an excel forum, and they suggested having them combined using a SQL script instead. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 22:13:44

[code]
SELECT ReorderDirections.FacID, Reorders.PatID,
Reorders.LastRxNo, Patients.PatLName,
Patients.PatFName, Reorders.DrugLabelName, ReorderDirections.Sig,
STUFF((SELECT ',' + AdminTime FROM Pat.dbo.ReordersAdminTimes ReordersAdminTimes
WHERE ReordersAdminTimes.FacID = Patients.FacID
AND ReordersAdminTimes.FacID = ReorderDirections.FacID AND ReordersAdminTimes.FacID = Reorders.FacID
AND ReordersAdminTimes.PatID = Patients.PatID AND ReordersAdminTimes.PatID = ReorderDirections.PatID
AND ReordersAdminTimes.PatID = Reorders.PatID AND ReordersAdminTimes.RoNo = ReorderDirections.RoNo
AND ReordersAdminTimes.RoNo = Reorders.RoNo AND ReordersAdminTimes.Split = ReorderDirections.Split
ORDER BY AdminTime
FOR XML PATH('')),1,1,'') AS AdminTime
FROM Pat.dbo.Patients Patients, Pat.dbo.ReorderDirections ReorderDirections,
Pat.dbo.Reorders Reorders
WHERE ReorderDirections.FacID = Patients.FacID AND ReorderDirections.PatID = Patients.PatID
AND Reorders.FacID = Patients.FacID AND Reorders.FacID = ReorderDirections.FacID
AND Reorders.PatID = Patients.PatID AND Reorders.PatID = ReorderDirections.PatID
AND Reorders.RoNo = ReorderDirections.RoNo
AND ((Patients.PatStatus='A') AND (Reorders.CutoffDt Is Null))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cordeparker
Starting Member

5 Posts

Posted - 2012-07-17 : 14:14:35

Wow, that is exactly what I needed. Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 14:43:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cordeparker
Starting Member

5 Posts

Posted - 2012-07-17 : 15:31:08

how about this one....same concept but with the ReorderDirections.Sig column needing to be combined with a ";" when the Reorders.LastRxNo are the same.

SELECT Patients.FacID, Patients.NsID, Reorders.LastRxNo, Patients.PatLName, Patients.PatFName, Reorders.DrugLabelName, ReorderDirections.Sig, KeyIdentifiers.DeaClass, Reorders.DateWritten, Reorders.PhRxExpireDate, Reorders.PhName, Reorders.TotalQtyWritten, Reorders.TotalQtyDispensed
FROM Drug.dbo.KeyIdentifiers KeyIdentifiers, Pat.dbo.Patients Patients, Pat.dbo.ReorderDirections ReorderDirections, Pat.dbo.Reorders Reorders
WHERE Reorders.FacID = Patients.FacID AND Reorders.PatID = Patients.PatID AND ReorderDirections.FacID = Patients.FacID AND ReorderDirections.FacID = Reorders.FacID AND ReorderDirections.PatID = Patients.PatID AND ReorderDirections.PatID = Reorders.PatID AND ReorderDirections.RoNo = Reorders.RoNo AND KeyIdentifiers.NDC = Reorders.NDC AND ((KeyIdentifiers.DeaClass<>'') AND (Patients.PatStatus='A') AND (Reorders.CutoffDt Is Null))

I can post a picture if you need

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 22:59:04

would be better if you can post a sample

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cordeparker
Starting Member

5 Posts

Posted - 2012-07-18 : 13:28:15

This is what it looks like

and this is what I want it to look like

Notice the ones I highlighted. Same thing. When the LastRXNo Match, merge the cells and combine the Sig with a ";"
Thanks again Vishkh, you've been a lot of help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 16:46:43

its same way


;With TempTable
AS
(
SELECT columns...,
ROW_NUMBER() OVER (PARTITION BY FacID,NsID,LastRxNo ORDER BY Sig) AS Rn
FROM Table
)

SELECT all other columns...,
STUFF((SELECT ';' FROM TempTable WHERE facID = t.FacID AND NsID = t.NsID AND LastRxNo = t.LastrxNo AND Rn >1 FOR XML PATH('')),1,1,'') AS Sig
FROM TempTable t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cordeparker
Starting Member

5 Posts

Posted - 2012-07-26 : 13:14:08

Sorry, I'm still having trouble. I'm trying to learn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 14:20:07

quote:
Originally posted by cordeparker

Sorry, I'm still having trouble. I'm trying to learn

can you elaborate on what issue you're facing with some data. Without that we wont be able to understand issue as we cant access your tables or see your s/m

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources