put 2 lines into 1 column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Toralf Förster - 10 Apr 2008 09:07 GMT

IHello,

I've a table like this (location withprobably more than i ip address) :

NAME    IP_ADDRESS
host1    1.1.x
host1    1.2.x
host2    2.1.x
host2    2.2.x
host3    3.x
host4    4.x

and try to get this output (but only with db2, not with awk, sed or sth
similar nice UNIX tool) :

host1    1.1.x    1.2.x
host2    2.1.x    2.2.x
host3    3.x
host4    4.x

I' am really struggling but didn't found a solution yesterday evening
... :-(

Signature

Toralf Förster
pgp key 0x7DB69DA3

Reply to this Message

Serge Rielau - 10 Apr 2008 12:13 GMT

> IHello,
>
[quoted text clipped - 18 lines]
> I' am really struggling but didn't found a solution yesterday evening
> ... :-(

Download my "SQL on Fire" slides from the web (google). There is a PIVOT
and an UNPIVOT example.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

jefftyzzer - 10 Apr 2008 17:51 GMT

> IHello,
>
[quoted text clipped - 22 lines]
> Toralf Förster
> pgp key 0x7DB69DA3

Toralf:

I adapted the following SQL from Anthony Molinaro's _SQL Cookbook_.
See if it helps you. Incidentally, I can't say enough good things
about Serge's "SQL on Fire" presentations--you really should get them
(go here: http://www.iiug.org/waiug/present/Forum2006/Forum2006.html).

WITH
    PERSON_NAME
    (
    PRSN_NM_ID,
    PRSN_ID,
    FIRST_NM
    )
AS
    (VALUES (1,1,'JEFF'),
            (2,1,'JEFFREY'),
            (3,2,'EDDIE'),
            (4,2,'EDWARD'),
            (5,2,'TED'),
            (6,3,'ELIZABETH'),
            (7,3,'BETSY')
    ),
    PRSN_NM
    (
    PRSN_ID,
    CNT,
    LIST,
    PRSN_NM_ID,
    LEN
    )
AS
    (
    SELECT
        PN1.PRSN_ID,
        COUNT(*) OVER (PARTITION BY PN1.PRSN_ID),
        CAST(PN1.FIRST_NM AS VARCHAR(4000)),
        PN1.PRSN_NM_ID,
        1
    FROM
        PERSON_NAME PN1
    UNION ALL
    SELECT
        PRSN_NM.PRSN_ID,
        PRSN_NM.CNT,
        PRSN_NM.LIST||', '||PN2.FIRST_NM,
        PN2.PRSN_NM_ID,
        PRSN_NM.LEN + 1
    FROM
        PERSON_NAME PN2,
        PRSN_NM
    WHERE
        PN2.PRSN_ID = PRSN_NM.PRSN_ID
    AND
        PN2.PRSN_NM_ID > PRSN_NM.PRSN_NM_ID
    )
SELECT
    LIST
FROM
    PRSN_NM
WHERE
    LEN = CNT;

Regards,

--Jeff

Reply to this Message

Toralf Förster - 14 Apr 2008 15:18 GMT

> Toralf:
>
[quoted text clipped - 6 lines]
>
> --Jeff

Thanks Jeff, I'use that method.

Signature

Toralf Förster
pgp key 0x7DB69DA3

Reply to this Message

jefftyzzer - 10 Apr 2008 18:00 GMT

> IHello,
>
[quoted text clipped - 22 lines]
> Toralf Förster
> pgp key 0x7DB69DA3

Toralf:

I adapted the following SQL from Anthony Molinaro's _SQL Cookbook_.
See if it helps you. Incidentally, I can't say enough good things
about Serge's "SQL on Fire" presentations--you really should get them
(go here: http://www.iiug.org/waiug/present/Forum2006/Forum2006.html).

WITH
PERSON_NAME
(
PRSN_NM_ID,
PRSN_ID,
FIRST_NM
)
AS
(VALUES (1,1,'JEFF'),
(2,1,'JEFFREY'),
(3,2,'EDDIE'),
(4,2,'EDWARD'),
(5,2,'TED'),
(6,3,'ELIZABETH'),
(7,3,'BETSY')
),
PRSN_NM
(
PRSN_ID,
CNT,
LIST,
PRSN_NM_ID,
LEN
)
AS
(
SELECT
PN1.PRSN_ID,
COUNT(*) OVER (PARTITION BY PN1.PRSN_ID),
CAST(PN1.FIRST_NM AS VARCHAR(4000)),
PN1.PRSN_NM_ID,
1
FROM
PERSON_NAME PN1
UNION ALL
SELECT
PRSN_NM.PRSN_ID,
PRSN_NM.CNT,
PRSN_NM.LIST||', '||PN2.FIRST_NM,
PN2.PRSN_NM_ID,
PRSN_NM.LEN + 1
FROM
PERSON_NAME PN2,
PRSN_NM
WHERE
PN2.PRSN_ID = PRSN_NM.PRSN_ID
AND
PN2.PRSN_NM_ID > PRSN_NM.PRSN_NM_ID
)
SELECT
PRSN_ID,
LIST
FROM
PRSN_NM
WHERE
LEN = CNT
ORDER BY
PRSN_ID;

Regards,

--Jeff

Reply to this Message

jefftyzzer - 10 Apr 2008 18:04 GMT

> IHello,
>
[quoted text clipped - 22 lines]
> Toralf Förster
> pgp key 0x7DB69DA3

Toralf:

I adapted the following SQL from Anthony Molinaro's _SQL Cookbook_.
See if it helps you. Incidentally, I can't say enough good things
about Serge's "SQL on Fire" presentations--you really should get them
(go here: http://www.iiug.org/waiug/present/Forum2006/Forum2006.html).

WITH
PERSON_NAME
(
PRSN_NM_ID,
PRSN_ID,
FIRST_NM
)
AS
(VALUES (1,1,'JEFF'),
(2,1,'JEFFREY'),
(3,2,'EDDIE'),
(4,2,'EDWARD'),
(5,2,'TED'),
(6,3,'ELIZABETH'),
(7,3,'BETSY')
),
PRSN_NM
(
PRSN_ID,
CNT,
LIST,
PRSN_NM_ID,
LEN
)
AS
(
SELECT
PN1.PRSN_ID,
COUNT(*) OVER (PARTITION BY PN1.PRSN_ID),
CAST(PN1.FIRST_NM AS VARCHAR(4000)),
PN1.PRSN_NM_ID,
1
FROM
PERSON_NAME PN1
UNION ALL
SELECT
PRSN_NM.PRSN_ID,
PRSN_NM.CNT,
PRSN_NM.LIST||', '||PN2.FIRST_NM,
PN2.PRSN_NM_ID,
PRSN_NM.LEN + 1
FROM
PERSON_NAME PN2,
PRSN_NM
WHERE
PN2.PRSN_ID = PRSN_NM.PRSN_ID
AND
PN2.PRSN_NM_ID > PRSN_NM.PRSN_NM_ID
)
SELECT
PRSN_ID,
LIST
FROM
PRSN_NM
WHERE
LEN = CNT
ORDER BY
PRSN_ID;

PRSN_ID
LIST
-----------
--------------------------------------------------------------------------------------------------------------------
SQL0347W The recursive common table expression "JTYZZER.PRSN_NM" may
contain
an infinite loop. SQLSTATE=01605

1 JEFF,
JEFFREY
2 EDDIE, EDWARD,
TED
3 ELIZABETH,
BETSY

3 record(s) selected with 1 warning messages printed.

Regards,

--Jeff

Reply to this Message