PIVOT STYLE REPORT OUTPUT

[Jen123]

Hi,

I have a table that contains for example a person ID and products associated with that person. If I do a select * where personid = 1, I would get for example this

PersonID Product
1 prod1
1 prod2
1 prod3

What I want is an out put that does this

PersonID Product Product Product
1 prod1 prod2 prod3

or even this would be ok

PersonID product
1 prod1,prod2,prod3


Does anyone know how to achieve this???

 

[dnoeth]

If the maximum number of products per personid is known in advance (and small), you can write quite efficient SQL:

sel
databasename
,tablename
,max(case when rnk = 1 then ColumnName else '' end) as Col1
,max(case when rnk = 2 then ColumnName else '' end) as Col2
,max(case when rnk = 3 then ColumnName else '' end) as Col3
,max(case when rnk = 4 then ColumnName else '' end) as Col4
,max(case when rnk = 5 then ColumnName else '' end) as Col5
,max(case when rnk = 6 then ColumnName else '' end) as Col6
,max(case when rnk = 7 then ColumnName else '' end) as Col7
,max(case when rnk = 8 then ColumnName else '' end) as Col8
from
(
sel
databasename
,tablename
,columnName
,rank() over (partition by databasename, tablename
order by columnid) as rnk
from
dbc.columns
where databasename = 'dbc'
and tablename in ('dbcinfo','Tables')
) dt
group by 1,2
order by 1,2
;

Or returning a concatenated string:

sel
databasename
,tablename
,trim(max(case when rnk = 1 then ColumnName else '' end)) ||
trim(max(case when rnk = 2 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk = 3 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk = 4 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk = 5 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk = 6 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk = 7 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk = 8 then ',' || ColumnName else '' end)) as Columns
from
(
sel
databasename
,tablename
,columnName
,rank() over (partition by databasename, tablename
order by columnid) as rnk
from
dbc.columns
where databasename = 'dbc'
and tablename in ('dbcinfo','Tables')
) dt
group by 1,2
order by 1,2
;


In both cases there will be some data lost, if there are more than 8 rows per value.

You can return a marker indicating missing data:
...
trim(max(case when rnk = 8 then ',' || ColumnName else '' end)) ||
trim(max(case when rnk > 8 then ',...' else '' end)) as Columns


Or you can add a new row for each group of 8 rows:

sel
databasename
,tablename
,trim(((rnk / 8) * 8) + 1 (format '999')) || ' to ' ||
trim(((rnk / 8) + 1) * 8 (format '999')) as Columns
,max(case when rnk mod 8 = 0 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 1 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 2 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 3 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 4 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 5 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 6 then ColumnName else '' end) (title '')
,max(case when rnk mod 8 = 7 then ColumnName else '' end) (title '')
from
(
sel
databasename
,tablename
,columnName
,rank() over (partition by databasename, tablename
order by columnid) - 1 as rnk
from
dbc.columns
where databasename = 'dbc'
and tablename in ('dbcinfo','Tables')
) dt
group by 1,2,3
order by 1,2,3
;


Dieter

 

[Jen123]

Many thanks for this, it will take me a while to work through the examples. I'll keep ya posted!!

 

[Jen123]

Ok, I'm struggling to get this to work. These are the parameters that I have

DATABASENAME = CSBTU
TABLENAME = R375_DORM_INC_MRK_LINK
COLUMNNAME = LINKED_ACCOUNT_NO
COLUMNID = QCID_PERSON_NO

And if I do the first example, I'm hoping to see this

PERSONID PROD1 PROD1
QCID_PERSON_NO LINKED_ACCOUNT_NO LINKED_ACCOUNT_NO

 

[dnoeth]

The queries are just examples how to do it using a table which exists on every Teradatasystem: dbc.columns

Just cut&paste the queries it into Queryman and execute it.

You're probably confused because of Databasename, TableName, ColumnName ;-)

sel
QCID_PERSON_NO AS PersonID
,max(case when rnk = 1 then LINKED_ACCOUNT_NO else '' end) as Col1
,max(case when rnk = 2 then LINKED_ACCOUNT_NO else '' end) as Col2
,max(case when rnk = 3 then LINKED_ACCOUNT_NO else '' end) as Col3
,max(case when rnk = 4 then LINKED_ACCOUNT_NO else '' end) as Col4
,max(case when rnk = 5 then LINKED_ACCOUNT_NO else '' end) as Col5
,max(case when rnk = 6 then LINKED_ACCOUNT_NO else '' end) as Col6
,max(case when rnk = 7 then LINKED_ACCOUNT_NO else '' end) as Col7
,max(case when rnk = 8 then LINKED_ACCOUNT_NO else '' end) as Col8
from
(
sel
QCID_PERSON_NO
,LINKED_ACCOUNT_NO
,rank() over (partition by QCID_PERSON_NO
order by LINKED_ACCOUNT_NO) as rnk
from
CSBTU.R375_DORM_INC_MRK_LINK
) dt
group by 1
order by 1
;

Dieter

 

[Jen123]

You're right about that!!
Thank you very much, it's just what I needed.
Incidentally, where did you find the examples, are they in some kind of book/manual or is that a trade secret?

 

[dnoeth]

As i'm a SQL & Teradata instructor, i need quite a lot of examples and i usually write it myself ;-)

In fact this one is just a modified/enhanced Teradata version of a common approach you usually find in advanced SQL books.

But there's no advanced SQL book for Teradata :-(
So i usually recommend Joe Celko's "SQL for Smarties", he's trying to solve problems based on Standard SQL and you often can port it to Teradata (although it's missing OLAP function, which provide neat solutions to lots of problems).

Dieter

 

[TheMartian]

Jen123

I find "TERADATA SQL UNLEASH THE POWER" by Larkins and Coffing a useful additon to the NCR manuals

Martin

 

[dnoeth]

IMHO the title "Unleash the Power" is misleading.
It's mainly beginner level with almost no advanced stuff, it was very disapointing to me. You'll hardly find anything which isn't covered in the Teradata manuals also.
After i bought it i recognized they use it as training material in their basic SQL classes.

Dieter

 

[Jen123]

Yeah I've got that book, I do find it more useful on the administration side of things as opposed to output type sql. Thanks for all the tips tho