• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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).
0
tselectro
Asked:
tselectro
1 Solution
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now