tselectro
asked on
Show all data from first row, hide some data from the next rows
Hello, I have two (example) tables.
Table1:
ID
DateTime
Table2:
ID
Table1_ID
Field1
Field2
I want to show all data for first row for every "group by Table1.DateTime", but "hide" DateTime for following rows. Like:
Table1.ID Table2.ID Table1.DateTime, Table2.Field1, Table2.Field2
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
1 1 2013-01-29 Field1 info Field2 info
1 2 (hide this value) xxxx1 yyyyyy1
1 3 (hide this value) xxxx2 yyyyyy2
2 4 2013-01-30 xxxx3 yyyyyy3
I use a very simple view/grid and want to "group" the information based on DateTime. Any sql syntax tips (standard LEFT/INNER JOIN show all data, not what I want).
Table1:
ID
DateTime
Table2:
ID
Table1_ID
Field1
Field2
I want to show all data for first row for every "group by Table1.DateTime", but "hide" DateTime for following rows. Like:
Table1.ID Table2.ID Table1.DateTime, Table2.Field1, Table2.Field2
--------------------------
1 1 2013-01-29 Field1 info Field2 info
1 2 (hide this value) xxxx1 yyyyyy1
1 3 (hide this value) xxxx2 yyyyyy2
2 4 2013-01-30 xxxx3 yyyyyy3
I use a very simple view/grid and want to "group" the information based on DateTime. Any sql syntax tips (standard LEFT/INNER JOIN show all data, not what I want).
Hi,
Though i have not tested it you could try something like the following:
Giannis
Though i have not tested it you could try something like the following:
SELECT T1.ID AS T1_ID,
MIN(T2.ID) AS T2_ID,
CASE WHEN T3.T1_ID IS NULL THEN NULL ELSE T1.DateTime END,
T2.Field1,
T2.Field2
FROM Table1 T1
JOIN Table2 T2
ON T1.ID = T2.Table1_ID
LEFT JOIN
(
SELECT T1.ID AS T1_ID,
MIN(T2.ID) AS T2_ID
FROM Table1 T1
JOIN Table2 T2
ON T1.ID = T2.Table1_ID
GROUP BY T1.ID
) T3
ON T1.ID = T3.T1_ID
AND T2.ID = T3.T2_ID
Giannis
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT
a_id 'Table1.ID',
b_id 'Table2.ID',
CASE WHEN RR = 1 THEN DateTime ELSE null END 'DateTime',
Field1,
Field2
FROM
(
SELECT
(ROW_NUMBER() OVER(PARTITION BY a.ID, a.DateTime ORDER BY a.ID, b.ID)) [RR], a.ID as 'a_id',b.ID as 'b_id',a.DateTime, b.Table1_ID, b.Field1, b.Field2
FROM Table1 a inner join Table2 b on b.Table1_ID = a.ID ) cte