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).
tselectroAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT
    t1.ID AS [Table1.ID], t2.ID AS [Table2.ID],
    CASE WHEN t2.row_num = 1 THEN CONVERT(varchar(10), t1.DateTime, 120) ELSE '' END AS [Table1.DateTime],
    t2.Field1 AS [Table2.Field1], t2.Field2 AS [Table2.Field2]
FROM (
    SELECT
        ID, Table1_ID, Field1, Field2,
        ROW_NUMBER() OVER ( PARTITION BY Table1_ID ORDER BY ID) AS row_num
    FROM dbo.Table2
) AS t2
INNER JOIN dbo.Table1 t1 ON
    t1.ID = t2.Table1_ID
0
 
topdog770Commented:
Hi ts,

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
0
 
Ioannis ParaskevopoulosCommented:
Hi,

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

Open in new window


Giannis
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.