All Forums Database
pavan.polish 18 posts Joined 09/12
30 Apr 2014
Transpose and Concatenating Values

Hi,
I have a requirement as below:-
the table structure is as below:-
ID - VALUE
id1 - val1
id2 - val2
id3 - val3
id3 - val4
id4 - val5
id4 - val6
id4 - val7
id4 - val8
requirement is to concatenate all values and put in one column for each ID with a limiatation of considering only 2 values.
Hence, I want to generate output as below:-
ID - NEW_VALUE
id1 - val1
id2 - val2
id3 - val3; val4
Here id4 is not considered for the output as it has 4 values.
Can anyone advise how this can be achieved in Teradata using queries.
Thanks a lot in Advance.
Sagar

dnoeth 4628 posts Joined 11/04
01 May 2014

Hi Sagar,
only two values?
Then it's easy:

SELECT
   ID, 
   MIN(VALUE) ||
   CASE 
      WHEN COUNT(*) = 1 THEN '' 
      ELSE ';' || MAX(value)  
   END AS NEW_VALUE
FROM tab
GROUP BY 1
HAVING COUNT(*) <= 2

 

Dieter

pavan.polish 18 posts Joined 09/12
01 May 2014

Hi Dieter,
Thanks for the query and it is working to concatenate only MIN and MAX values.
I think I need to provide more details about the requirement:-
ID - VALUE
id1 - val11
id2 - val22
id2 - val33
id3 - val33
id3 - val54
id3 - val67
id4 - val13
id4 - val14
id4 - val18
id4 - val90
id5 - val13
id5 - val14
id5 - val18
id5 - val90
id5 - val91
Actual requirement is for 10 values but, to minimize requested logic for 2 values.
Could you please advise to extract 4 or less than 4 values by concatenating all the values.
The expected output should be as below:-
ID - NEW_VALUE
id1 - val11
id2 - val22; val33
id3 - val33; val54; val67
id4 - val13; val14; val18; val90
Thanks a lot in advance.
Sagar
 

Qaisar Kiani 337 posts Joined 11/05
02 May 2014

You need to write the recursive query.
I haven't tested this SQL, but I hope it will work for you

WITH RECURSIVE LOOKUP(ID,VALUE,LVL)
AS
(
	SELECT ID, MIN(VALUE(VARCHAR(1000))) AS VALUE,1 as LVL
	FROM table1
	GROUP BY 1

	UNION ALL

	SELECT b.ID,trim(a.VALUE) || ';' || trim( b.VALUE), LVL+1
	FROM table1 a INNER JOIN LOOKUP b
	ON a.ID = b.ID
	AND a.VALUE > b.VALUE
)

SELECT ID, VALUE, LVL
FROM LOOKUP
QUALIFY RANK() OVER(PARTITION BY id ORDER BY VALUE DESC) = 1;

 

You must sign in to leave a comment.