Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQLSERVER 2000 SQL UNION Expand / Collapse
Author
Message
Posted Friday, February 18, 2011 2:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
Gianluca Sartori

You behave yourself please
Don't let your accusation on this thread go sideway.
Be careful in your driving



Once the helper got my coding working,
I will posting the working codes here to share with others encounter similar problem.

And you behave yourself and control you abusing statement.
Post #1066228
Posted Friday, February 18, 2011 4:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
string strsql = "";
strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";
strsql += " Union Select CustomerId, CompanyName ";
strsql += " From testCustomers ";
strsql += " Order by CompanyName ";




Far away is close at hand in the images of elsewhere.

Anon.

Post #1066263
Posted Friday, February 18, 2011 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
David Burrows (2/18/2011)
string strsql = "";
strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";
strsql += " Union Select CustomerId, CompanyName ";
strsql += " From testCustomers ";
strsql += " Order by CompanyName ";



You can run into a problem with that, if there's a company name that comes earlier in the sort sequence than the "column header". I usually add a "Sequence" column, hard-coded to 0 for the header, and 1 for the query under the union, and then order by that first, to force the header to stay on top. (Had to do that in a few places in an Access front-end application.)

Just something to think about on it.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1066378
Posted Friday, February 18, 2011 7:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
GSquared (2/18/2011)
David Burrows (2/18/2011)
string strsql = "";
strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";
strsql += " Union Select CustomerId, CompanyName ";
strsql += " From testCustomers ";
strsql += " Order by CompanyName ";



You can run into a problem with that, if there's a company name that comes earlier in the sort sequence than the "column header". I usually add a "Sequence" column, hard-coded to 0 for the header, and 1 for the query under the union, and then order by that first, to force the header to stay on top. (Had to do that in a few places in an Access front-end application.)

Just something to think about on it.


Yep knew that, I've had to do the same as you several times
But took it that the select text has a leading space would overcome that (unless there is a CompanyName with a leading space)



Far away is close at hand in the images of elsewhere.

Anon.

Post #1066399
Posted Friday, February 18, 2011 3:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
Hullo David Burrows,

Thank you very much for sharing information with me in coding that makes me understand it logically.

Appreciate your generosity in helping with sample coding.
You are wonderful. I will recommend you to my office other IT Progammers.

I will try out your suggestion and once I got it working I will post it here to share with others who may have encounter similar problems.

I am so glad and happy to meet you here.
Post #1066695
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse