Is it possible to write the following in one select?

Question

Post reply

Is it possible to write the following in one select?

Rob Reid-246754

SSCarpal Tunnel

Points: 4183
More actions
November 22, 2007 at 6:39 am

#181320

I am trying to write a report of no importance apart from my own use and although I can
get the data out in the format that I require using a couple of temp table vars I really
want to be able to write the report in one select if possible. I am almost there apart from the ordering.
The report is basically selecting data from one table but listing the rows in two columns (evenly balanced) so that as the data grows the columns grow evenly (apart from odd numbers).
The left column needs to go from row 1 to the halfway marker and then the second column from the halfway to the last record. So the data comes out like this
SitePK SiteName SiteTag SitePK SiteName SiteTag
1 Site A TA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 1

Sorry my computer spazzed out and somehow sent the message before I wanted it to.

I am trying to write a report of no importance apart from my own use and although I can

get the data out in the format that I require using a couple of temp table vars I really

want to be able to write the report in one select if possible. I am almost there apart from the ordering.

The report is basically selecting data from one table but listing the rows in two columns (evenly balanced) so that as the data grows the columns grow evenly (apart from odd numbers).

The left column needs to go from row 1 to the halfway marker and then the second column from the halfway to the last record. So the data comes out like this

SitePK SiteName SiteTag SitePK SiteName SiteTag

1 Site A TA 4 Site D BAS

2 Site B TBD 5 Site E

3 Site C CCD NULL NULL NULL

Now the SQL that I've got to work using temp tables is this

DECLARE @Recs int,

@Half decimal(10,2),

@MaxHalf int,

@MinHalf int,

@MaxSitePk int

SELECT@Recs = COUNT(SitePK), @Half = cast(@Recs as decimal(10,2))/2,

@MaxHalf = CEILING(@Half),@MinHalf = FLOOR(@half)

FROM SITES

DECLARE @SITES1 TABLE(Rows int identity(1,1),SitePK int,SiteName varchar(100),SiteTag varchar(5))

DECLARE @SITES2 TABLE(Rows int identity(1,1),SitePK int,SiteName varchar(100),SiteTag varchar(5))

INSERT INTO @SITES1

SELECT TOP(@MaxHalf) SitePk,SiteName,SiteTag

FROMSITES

ORDER BY SitePK ASC

SELECT @MaxSitePK=MAX(SitePk) FROM @SITES1

INSERT INTO @SITES2

SELECT TOP(@MinHalf) SitePk,SiteName,SiteTag

FROMSITES

WHERESitePk>@MaxSitePk

ORDER BY SitePK ASC

SELECTs1.SitePK, s1.Sitename, s1.SiteTag,

s2.SitePK, s2.Sitename, s2.SiteTag

FROM@SITES1 as s1

LEFT JOIN

@SITES2 as s2

ONs1.Rows = s2.RowsDECLARE @Recs int,

@Half decimal(10,2),

@MaxHalf int,

@MinHalf int,

@MaxSitePk int

SELECT@Recs = COUNT(SitePK), @Half = cast(@Recs as decimal(10,2))/2,

@MaxHalf = CEILING(@Half),@MinHalf = FLOOR(@half)

FROM SITES

DECLARE @SITES1 TABLE(Rows int identity(1,1),SitePK int,SiteName varchar(100),SiteTag varchar(5))

DECLARE @SITES2 TABLE(Rows int identity(1,1),SitePK int,SiteName varchar(100),SiteTag varchar(5))

INSERT INTO @SITES1

SELECT TOP(@MaxHalf) SitePk,SiteName,SiteTag

FROMSITES

ORDER BY SitePK ASC

SELECT @MaxSitePK=MAX(SitePk) FROM @SITES1

INSERT INTO @SITES2

SELECT TOP(@MinHalf) SitePk,SiteName,SiteTag

FROMSITES

WHERESitePk>@MaxSitePk

ORDER BY SitePK ASC

SELECTs1.SitePK, s1.Sitename, s1.SiteTag,

s2.SitePK, s2.Sitename, s2.SiteTag

FROM@SITES1 as s1

LEFT JOIN

@SITES2 as s2

ONs1.Rows = s2.Rows

which does what I want but I am wondering and trying to get it to output in as little SQL as possible.

I am sure there is a way of doing it in SQL 2005 with the Row_Number() function. All I can manage so far is this.

DECLARE @Recs int,

@Half decimal(10,2),

@MaxHalf int,

@MinHalf int,

@MaxSitePk int

SELECT@Recs = COUNT(SitePK), @Half = cast(@Recs as decimal(10,2))/2,

@MaxHalf = CEILING(@Half),@MinHalf = FLOOR(@half)

FROMSITES

SELECTs1.SitePK, s1.SiteName, s1.SiteTag,

s2.SitePK, s2.SiteName, s2.SiteTag

FROM(

SELECT TOP (@MaxHalf) ROW_NUMBER() OVER (ORDER BY SitePK ASC) as Rows,

SitePk,SiteName,SiteTag

FROMSITES

) as s1

LEFT JOIN

(SELECT TOP (@MinHalf) ROW_NUMBER() OVER (ORDER BY SitePK DESC) as Rows,

SitePk,SiteName,SiteTag

FROMSITES

) as s2 ONs1.Rows = s2.Rows

But as I am ordering the second sub-select DESC the second column starts from the last number and works its way down rather than starting from the last number from the 1st columm. If I order both ASC then the rows are duplicated so I need a way of the second column sub-select only selecting records with SitePk > 1stColumn Last SitePk.

Also the SitePks do not relate to the row number in any way.

Its not very important and I could just use the first SQL but for my own gratification and for me just wanting to know if its possible and how (in as less SQL as possible) I would like a solution.

Thanks in advance to anyone who looks at it.

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 2

Hi Rob,

This is my first post to this group, but I do find it an invaluable resource so I thought I'd try giving something back.

This query requires SQL 2005 for the new ranking functions, and the Common Table Expressions. I've tested it for odd numbers, which is fine. In order to split to more than two columns, the NTile function and the last line would need some changes.

I'd be interested in other's thoughts? I've left the SELECT * in at the end so you can see the columns of the ranking functions and how they work together. I'm self-taught in SQL over the last three years, so let me know if I've made any terrible errors 🙂

--Declare @Table, as per your original data table

DECLARE @Table TABLE

(

SitePK INT IDENTITY(1,1) NOT NULL,

SiteName NVARCHAR(20) NOT NULL,

SiteTag NVARCHAR(10) NOT NULL

)

--Fill @Table with sample data to use in query