Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-30
3
Medium Priority
?
256 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

609 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