January 31, 2007 at 12:05 pm
Hi,
I have to generate an extract file that has header, footer and the bulk of the data in the middle. Something like this
CREATE TABLE tblContent (content varchar(2))
INSERT INTO tblContent (content) VALUES ('a')
INSERT INTO tblContent (content) VALUES ('c')
INSERT INTO tblContent (content) VALUES ('b')
INSERT INTO tblContent (content) VALUES ('e')
INSERT INTO tblContent (content) VALUES ('d')
SELECT 'Header'
UNION ALL
SELECT content FROM tblContent
UNION ALL
SELECT 'Footer'
What I couldn't do is to make sure that the values in the middle area are sorter. In other words you have something like
Header
a
b
c
d
e
Footer
instead of
Header
a
c
b
e
d
Footer
Any help would be much appreciated.
January 31, 2007 at 12:14 pm
That'll work.
CREATE TABLE tblContent (content varchar(2))
INSERT INTO tblContent (content) VALUES ('a')
INSERT INTO tblContent (content) VALUES ('c')
INSERT INTO tblContent (content) VALUES ('b')
INSERT INTO tblContent (content) VALUES ('e')
INSERT INTO tblContent (content) VALUES ('d')
SELECT 'Header' AS content, 1 AS Sort
UNION ALL
SELECT content, 2 AS Sort FROM tblContent
UNION ALL
SELECT 'Footer' aS content, 3 AS Sort
ORDER BY Sort, Content
DROP TABLE tblContent
January 31, 2007 at 12:26 pm
Sorry guys, my mistake, my scenario wasn't quite accurate. I try to simplify it as much as I could, but I end up getting it wrong.
Here I am try it again
CREATE TABLE tblContent (content varchar(2))
INSERT INTO tblContent (content) VALUES ('aa')
INSERT INTO tblContent (content) VALUES ('mc')
INSERT INTO tblContent (content) VALUES ('db')
INSERT INTO tblContent (content) VALUES ('le')
INSERT INTO tblContent (content) VALUES ('nd')
SELECT 'Header'
UNION ALL
SELECT content FROM tblContent ORDER BY substring(content,2,1))
UNION ALL
SELECT 'Footer'
The idea is that I have to sort by a string that is inside the content field.
The privious one one would have worked acceptably, not guarantied 100%, with something like
SELECT 'Header'
UNION ALL
SELECT content FROM (SELECT TOP 100 content FROM tblContent ORDER BY content) a
UNION ALL
SELECT 'Footer'
if you know about how many rows you are expecting in the table.
Thanks Ninja for help, but adding a Sort column would change the format of my output file.
January 31, 2007 at 12:41 pm
You don't need to output the sort column to use it in the order by :
CREATE TABLE tblContent (content varchar(2))
INSERT INTO tblContent (content) VALUES ('1a')
INSERT INTO tblContent (content) VALUES ('1c')
INSERT INTO tblContent (content) VALUES ('1b')
INSERT INTO tblContent (content) VALUES ('1e')
INSERT INTO tblContent (content) VALUES ('1d')
SELECT Content FROM
(
SELECT 'Header' AS content, 1 AS Sort
UNION ALL
SELECT content, 2 AS Sort FROM tblContent
UNION ALL
SELECT 'Footer' aS content, 3 AS Sort
) dt
ORDER BY dt.Sort, CASE WHEN dt.Sort = 2 THEN substring(content,2,1) ELSE Content END
DROP TABLE tblContent
January 31, 2007 at 1:37 pm
Thanks, works beautifully, nice technique.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply