Solved

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

Posted on 2013-01-30
3
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:
Scott Pletcher 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

749 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