locked
Need output in a special format RRS feed

  • Question

  • Hi Friends,

    I have a requirement i.e. I need output of my table in a specific format (as below).

    create table a 
            (
                machinename varchar(50),
                physicalmemory int,
                sqlinstancename varchar(50),
                maxmemory int
            )
    
    
    Insert into a values ('TestServer',1976,'SQL2K8',2147483647)
    Insert into a values ('TestServer',1976,'SQL2K',512)
    
    

    Note: PhysicalMemory & Maxmemory is in MB's

    Output Required

    MachineName         PhysicalMemory                SQLInstanceName           MaxMemory

    TestServer             1976                                   SQL2K8                            2147483647

                                                                               SQL2K                               512


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, September 25, 2012 3:56 AM

Answers

  • Try

    with set1 as
    (select MachineName,PhysicalMemory,SQLInstanceName,MaxMemory,row_number() over (partition by MachineName,PhysicalMemory order by SQLInstanceName) as rn
    from a)
    select case when rn=1 then MachineName else '' end as MachineName,case when rn=1 then PhysicalMemory else '' end as PhysicalMemory,SQLInstanceName,MaxMemory
    from set1
    order by MachineName,PhysicalMemory,rn;


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by GURSETHI Tuesday, September 25, 2012 4:07 AM
    Tuesday, September 25, 2012 4:06 AM

All replies

  • Try

    with set1 as
    (select MachineName,PhysicalMemory,SQLInstanceName,MaxMemory,row_number() over (partition by MachineName,PhysicalMemory order by SQLInstanceName) as rn
    from a)
    select case when rn=1 then MachineName else '' end as MachineName,case when rn=1 then PhysicalMemory else '' end as PhysicalMemory,SQLInstanceName,MaxMemory
    from set1
    order by MachineName,PhysicalMemory,rn;


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by GURSETHI Tuesday, September 25, 2012 4:07 AM
    Tuesday, September 25, 2012 4:06 AM
  • Hi Hua,

    Thanks dear, thanks for your help. Its working.


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, September 25, 2012 4:07 AM
  • You could try something like the following (Note: I added a few more test data rows)...

    create table a 
            (
                machinename varchar(50),
                physicalmemory int,
                sqlinstancename varchar(50),
                maxmemory int
            )
    
    
    Insert into a values ('TestServer',1976,'SQL2K8',2147483647)
    Insert into a values ('TestServer',1976,'SQL2K',512)
    Insert into a values ('TestServer1',1977,'SQL2K8',2147483647)
    Insert into a values ('TestServer1',1977,'SQL2K',512)
    Insert into a values ('TestServer1',1977,'SQL2005',1024)
    
    ;with cte1
    as
    (
    	select
    		machinename,
    		physicalmemory,
    		sqlinstancename,
    		maxmemory,
    		ROW_NUMBER() OVER (PARTITION BY machinename, physicalmemory order by machinename) as rownum
    	from
    		a
    )
    
    Select 
    	case
    		when rownum = 1 then machinename
    		else ''
    	end as machinename,
    	case
    		when rownum = 1 then cast(physicalmemory as varchar)
    		else ''
    	end as physicalmemory,
    	sqlinstancename,
    	maxmemory
    From 
    	cte1
    
    drop table a

    Thanks

    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem

    Tuesday, September 25, 2012 4:15 AM
  • create table #a 
            (
                machinename varchar(50),
                physicalmemory int,
                sqlinstancename varchar(50),
                maxmemory int
            )
    
    
    Insert into #a values ('TestServer',1976,'SQL2K8',2147483647)
    Insert into #a values ('TestServer',1976,'SQL2K',512)
    
    ;WITH CTE(machinename,physicalmemory,sqlinstancename,maxmemory,row)
    AS
    (
    SELECT 
    	machinename,
    	physicalmemory,
    	sqlinstancename,
    	maxmemory,
    	ROW_NUMBER() OVER(PARTITION BY machinename,physicalmemory ORDER BY machinename)AS row
    FROM #a
    )
    
    SELECT 
    		CASE row
    			WHEN 1 THEN machinename
    			ELSE ''
    		END AS machinename,	
    		CASE row
    			WHEN 1 THEN CAST(physicalmemory AS NVARCHAR(10))
    			ELSE ''
    		END AS physicalmemory,
    		sqlinstancename,
    		maxmemory		
    FROM CTE 
    
    Drop table #a
    
    

    • Proposed as answer by KDE Lakmal Tuesday, September 25, 2012 5:05 AM
    Tuesday, September 25, 2012 5:05 AM