CODEWITHSUNDEEP
sql-servert-sql
MertYılmaz
MertYılmaz

Reputation: 55

T sql group by only column value

I have columns as below in Products Table

name varchar(100)

xdate datetime

If i select below query from Products Table

select name
     , DateName( month , DateAdd( month , month(xdate) , 0 ) - 1 ) as xdate  
from Products

select query result as below

name (column) - xdate (column)

Computer    May

Phone   May

Mouse  May

Grass  April

Usb  April

Glass  February

Question:

How can i display result as below

name (column) - xdate (column)

    Computer    May

    Phone   

    Mouse  

    Grass  April

    Usb  

    Glass  February

I only want to display xdate once what to chage in my select query in order to achieve this.

Any help will be appreciated.

Thanks

Upvotes: 1

Views: 73

Answers (4)

ASh
ASh

Reputation: 35680

try this query 

select 
    name, 
    case when num = 1 then xdate else '' end as xdate
from
(
    select 
        name, 
        DateName( month , DateAdd( month , month(xdate) , 0 ) - 1 ) as xdate  ,
        row_number() over (partition by YEAR(XDATE), MONTH(XDATE) order by xdate) as num,
        xdate as real_date
    from Products
) T
order by real_date

Upvotes: 2

Stephan
Stephan

Reputation: 6018

For SQL 2012+, I think George has the best solution.

SQL 2005 Solution

SELECT  P.id,
        name,
        ISNULL(CA.xdate,'') AS xDate
FROM Products p
OUTER APPLY(
                SELECT DateName( month , DateAdd( month , month(xdate) , 0 ) - 1 )
                FROM Products
                GROUP BY xdate
                HAVING MIN(ID) = p.id
           ) CA(xdate)
ORDER BY ID

SQL 2008 Solution

SELECT  id,
        name,
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY CA.xDate ORDER BY ID) = 1
                THEN CA.xdate
            ELSE ''
        END AS xdate
FROM Products
CROSS APPLY(SELECT DateName( month , DateAdd( month , month(xdate) , 0 ) - 1 )) CA(xdate)
ORDER BY ID

Upvotes: 2

clweeks
clweeks

Reputation: 875

And a lower tech (less elegant) solution (that would work on SQL Server 2000):

select t.name, case when t.name = minSQ.minName then minSQ.mnth else '' end 
from Products t 
    join (select MIN(name) minName, DATENAME(month,xdate) mnth from Products group by DATENAME(month,xdate)) minSQ on DateName(month, t.xdate) = minSQ.mnth
order by t.xdate

Upvotes: 1

George T
George T

Reputation: 857

How is your data ordered? Assuming you have an ordering column (id in the example below), you can use the LAG function on SQL Server 2012 and higher:

DECLARE @Products TABLE
(id int, name varchar(100), xdate varchar(100))

INSERT INTO @Products (id, name, xdate)
VALUES
(1, 'Computer', 'May'),
(2, 'Phone', 'May'),
(3, 'Mouse', 'May'),
(4, 'Grass', 'April'),
(5, 'Usb', 'April'),
(6, 'Glass', 'February')

SELECT p.name,
    CASE LAG(p.xdate, 1,0) OVER (ORDER BY p.id)
        WHEN p.xdate THEN ''
        ELSE p.xdate
    END AS xdate
FROM @Products p

Results:

name        xdate
Computer    May
Phone   
Mouse   
Grass       April
Usb 
Glass       February

Upvotes: 2

Related Questions