Solved

Show all data from first row, hide some data from the next rows

Posted on 2013-01-30
3
248 Views
Last Modified: 2013-01-31
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
Comment
Question by:tselectro
3 Comments
 
LVL 12

Expert Comment

by:topdog770
ID: 38835567
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38835656
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 38835744
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now