I think it should be a Pivot

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tress - 30 Jul 2008 10:37 GMT

Hello,

Wondering if someone can help me with this, I have read quite a bit about
pivot tables but none of the solutions seem to be what I am after, I have
table data that looks like this

ID Address
1 The High Street
2 Chester
3 Cheshire
4 CH
5 The Road
6 Northhop
7 Flintshire
8 CH

Every 4 rows of data needs to become 1 row, this will be the case every time
so I want my data to look like

Column 1 Column2 Column3
Column4

The Hight Steet Chester Cheshire CH
The Road Northop Flintshire
CH

I have seen plenty of examples when on each row there is something to link
all 4 rows together but nothing where you only have one column of data.

I am using SQL Server 2005

Any help would be much appreciated.

Thanks

Reply to this Message

Razvan Socol - 30 Jul 2008 10:53 GMT

Hello, tress

Try something like this:

CREATE TABLE tbl (
    ID int PRIMARY KEY,
    Address varchar(50)
)

INSERT INTO tbl VALUES (1,'The High Street')
INSERT INTO tbl VALUES (2,'Chester')
INSERT INTO tbl VALUES (3,'Cheshire')
INSERT INTO tbl VALUES (4,'CH')
INSERT INTO tbl VALUES (5,'The Road')
INSERT INTO tbl VALUES (6,'Northhop')
INSERT INTO tbl VALUES (7,'Flintshire')
INSERT INTO tbl VALUES (8,'CH')

SELECT (ID-1)/4+1 as RowNum,
    MIN(CASE WHEN ID%4=1 THEN Address END) AS Street,
    MIN(CASE WHEN ID%4=2 THEN Address END) AS City,
    MIN(CASE WHEN ID%4=3 THEN Address END) AS County,
    MIN(CASE WHEN ID%4=0 THEN Address END) AS Code
FROM tbl GROUP BY (ID-1)/4+1

Signature

Razvan Socol
SQL Server MVP

Reply to this Message

Razvan Socol - 30 Jul 2008 11:01 GMT

> Hello, tress
>
[quoted text clipped - 20 lines]
> MIN(CASE WHEN ID%4=0 THEN Address END) AS Code
> FROM tbl GROUP BY (ID-1)/4+1

In SQL 2005 you can use the PIVOT operator, like this:

SELECT RowNum, Col0 as Street, Col1 as City, Col2 as County, Col3 as Code
FROM (
    SELECT (ID-1)/4+1 as RowNum,
        'Col'+CONVERT(char,(ID-1)%4) as ColNum,
        Address
    FROM tbl
) x PIVOT (MIN(Address) FOR ColNum IN (Col0, Col1, Col2, Col3)) AS p

But (to me) it seems more complicated and it will probably have the same
performance as the first query.

Signature

Razvan Socol
SQL Server MVP

Reply to this Message

tress - 30 Jul 2008 13:06 GMT

Hi,

Thanks to both of you for your responses, both as you have said do the same
thing, but I think its always worth knowing a couple of ways of doing the
same thing, you never know when it might come in handy.

Thanks again for the help!

> > Hello, tress
> >
[quoted text clipped - 33 lines]
> But (to me) it seems more complicated and it will probably have the same
> performance as the first query.

Reply to this Message