none
Loop RRS feed

  • Question

  • Dear's,

    I have a physical table like 

    EmpName Dept
    Tamil Software
    Selvan Software
    Kalai Software

    and i want to the resuly is like the below 

    EmpName Dept
    Tamil Software
    EmpName Dept
    Selvan Software
    EmpName Dept
    Kalai Software

    could you know the answer's Please post It.

    Sunday, September 22, 2019 10:23 AM

Answers

  • select  StockName,Segments,Amount ,rn,cn
    from(
    select *,count(*) over (partition   by rn) cn  from 
    (

    select StockName,cast(count(Segments) as varchaR(10)) Segments ,
    CAST(sum(Amount) AS VARCHAR(20)) Amount,row_number () over (order by StockName) rn 
     from @Temp group by StockName
    union all
    select 'StockName' StockName,'Segments' Segments, 'Amount' Amount,
    row_number () over (  order by StockName)  
    from @Temp 
    ) as der ) as der1 where cn=2 and rn<> 8 order by rn,StockName

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, September 22, 2019 12:44 PM
    Answerer

All replies

  • create table #t (c1 varchar(10),c2 varchar(10))
    insert into #t values ('Tamil','Software')
    insert into #t values ('Selvan','Software')
    insert into #t values ('Kalai','Software')

    select * from 
    (
    select c1,c2 ,row_number () over (order by c1) rn from #t
    union all
    select '' c1,'' c2,row_number () over (order by c1)  from #t
    ) as der
    order by rn desc ,c1 desc

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 22, 2019 10:32 AM
    Answerer
  • i want the result  like the below column name with data column name with data and i need a dynamic query because my physical table contains lakh of records are there

    EmpName

    Dept

    Tamil

    Software

    EmpName

    Dept

    Selvan

    Software

    EmpName

    Dept

    Kalai

    Software

    Sunday, September 22, 2019 10:45 AM
  • select  c1,c2 from
    (
    select c1,c2 ,row_number () over (order by c1) rn from #t
    union all
    select 'c1' c1,'c2' c2,row_number () over (order by c1)  from #t
    ) as der
    order by rn desc ,c1 desc

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 22, 2019 10:49 AM
    Answerer
  • it's not working for my requirement. anyway thanks for your answer..
    Sunday, September 22, 2019 11:06 AM
  • Can you explain what "it's not working for my requirement" means?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 22, 2019 11:08 AM
    Answerer
  • it's not working for my requirement. anyway thanks for your answer..

    Good day,

    To avoid the "Guessing Game" please provide:

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Regards,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, September 22, 2019 11:30 AM
    Moderator
  • i want the result  like the below column name with data column name with data and i need a dynamic query because my physical table contains lakh of records are there

    EmpName

    Dept

    Tamil

    Software

    EmpName

    Dept

    Selvan

    Software

    EmpName

    Dept

    Kalai

    Software

    So you are saying that for every row you want a header?

    No, you don't do that in T-SQL. In T-SQL you return data. Then you find some way to present it in your presentation layer (a UI, report writer etc)

    T-SQL and SQL Server is not a general-purpose environment. It is a highly specialised environment to store, update and retrieve data. It is not the place you implement fancy formatting.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 22, 2019 12:06 PM
  • the below are my queries

    Set NoCount ON 
    Declare @Temp Table (StockName Varchar(30),Segments Int,Amount Money)
    Insert Into @Temp Values('JetAirways',2,5000)
    Insert Into @Temp Values('airindia ',23,121214547266)
    Insert Into @Temp Values('spice',21,564557124554)
    Insert Into @Temp Values('scoot',2,5232)
    Insert Into @Temp Values('JetAirways',8,12121)
    Insert Into @Temp Values('spice',5,888)
    Insert Into @Temp Values('thaiairways',2,32121)
    Insert Into @Temp Values('lufthansa',1,5352)
    Insert Into @Temp Values('indigo',2,2212)
    Insert Into @Temp Values('goair',7,777)
    Insert Into @Temp Values('JetAirways',8,223213)
    Insert Into @Temp Values('thaiairways',9,4758745)
    Insert Into @Temp Values('airindia ',6,121)
    Insert Into @Temp Values('lufthansa',7,2)
    Insert Into @Temp Values('lufthansa',5,132)
    Insert Into @Temp Values('lufthansa',6,21)
    Insert Into @Temp Values('airindia ',1,132)
    Insert Into @Temp Values('scoot',3,7887)
    Declare @StockName Varchar(50)
    Declare @MaxRecordCount Int
    Set @MaxRecordCount=0
    select @MaxRecordCount=Count(*) from @Temp
    While @MaxRecordCount>0
    Begin
    select Top 1 @StockName=StockName from @Temp 
    Select StockName,count(Segments) 'Segments',Sum(Amount) 'Amount' 
    From @Temp 
    Where StockName = @StockName
    Group by StockName
    Delete from @Temp where StockName=@StockName
    Select @MaxRecordCount=count(*) from @Temp
    End

    Result

    StockName

    Segments

    Amount

    JetAirways

    3

    240334

    StockName

    Segments

    Amount

    airindia

    3

    121214547519

    StockName

    Segments

    Amount

    spice

    2

    564557125442

    StockName

    Segments

    Amount

    scoot

    2

    13119

    StockName

    Segments

    Amount

    thaiairways

    2

    4790866

    StockName

    Segments

    Amount

    lufthansa

    4

    5507

    StockName

    Segments

    Amount

    indigo

    1

    2212

    StockName

    Segments

    Amount

    goair

    1

    777

    I want to tune the above query because it takes more times to execute. so that i want to remove while if u have an idea for the same without using while loop.

    Sunday, September 22, 2019 12:17 PM
  • select  StockName,Segments,Amount ,rn,cn
    from(
    select *,count(*) over (partition   by rn) cn  from 
    (

    select StockName,cast(count(Segments) as varchaR(10)) Segments ,
    CAST(sum(Amount) AS VARCHAR(20)) Amount,row_number () over (order by StockName) rn 
     from @Temp group by StockName
    union all
    select 'StockName' StockName,'Segments' Segments, 'Amount' Amount,
    row_number () over (  order by StockName)  
    from @Temp 
    ) as der ) as der1 where cn=2 and rn<> 8 order by rn,StockName

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, September 22, 2019 12:44 PM
    Answerer
  • Well, rather than returning one row at a time, return all row at once. That will be quite a bit faster...

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 22, 2019 12:48 PM
  • Hi Tamil,

    The issue with your solution is presented by you in one word in the title = You use loop

    Your approach is wrong and does not fit to the way Tabular databases like SQL Server works. Tabular databases are designed to best work with tables or in other words with SET of data. This is not the same as working with loops in simple code that you write in your application (for example using C# or VB.net).

    The Tabular databases usually has built-in multiple algorithms in order to improve the way the server works with the data as SET and not row by row

    As Erland said, "rather than returning one row at a time, return all row at once."

    For example you can get the same results in one simple query which returns all as a single SET (table structure of result)

    Please check if the following solution fit your needs:

    SELECT 
    	t.StockName, Segments = COUNT(*), Amount = SUM(t.Amount)
    FROM @Temp t
    GROUP BY t.StockName
     

    The full example will be:

    Declare @Temp Table (StockName Varchar(30),Segments Int,Amount Money)
    Insert Into @Temp Values('JetAirways',2,5000)
    Insert Into @Temp Values('airindia ',23,121214547266)
    Insert Into @Temp Values('spice',21,564557124554)
    Insert Into @Temp Values('scoot',2,5232)
    Insert Into @Temp Values('JetAirways',8,12121)
    Insert Into @Temp Values('spice',5,888)
    Insert Into @Temp Values('thaiairways',2,32121)
    Insert Into @Temp Values('lufthansa',1,5352)
    Insert Into @Temp Values('indigo',2,2212)
    Insert Into @Temp Values('goair',7,777)
    Insert Into @Temp Values('JetAirways',8,223213)
    Insert Into @Temp Values('thaiairways',9,4758745)
    Insert Into @Temp Values('airindia ',6,121)
    Insert Into @Temp Values('lufthansa',7,2)
    Insert Into @Temp Values('lufthansa',5,132)
    Insert Into @Temp Values('lufthansa',6,21)
    Insert Into @Temp Values('airindia ',1,132)
    Insert Into @Temp Values('scoot',3,7887)
    
    SELECT 
    	t.StockName, Segments = COUNT(*), Amount = SUM(t.Amount)
    FROM @Temp t
    GROUP BY t.StockName
    * I have no idea if you want to delete the data as well or if you only used the delete in order to get the next value. This basically make no sense to me and not clear what is your real scenario


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Sunday, September 22, 2019 9:16 PM
    Moderator
  • Hi TamilShyni,

    I'm just wondering why you do not use statistic one if all the columns are not dynamical? 

    IF OBJECT_ID('Temp') IS NOT NULL drop table  Temp
    create table  Temp (StockName Varchar(30),Segments Int,Amount Money)
    insert into temp values 
    ('JetAirways',2,5000),
    ('airindia ',23,121214547266),
    ('spice',21,564557124554),
    ('scoot',2,5232),
    ('JetAirways',8,12121),
    ('spice',5,888),
    ('thaiairways',2,32121),
    ('lufthansa',1,5352),
    ('indigo',2,2212),
    ('goair',7,777),
    ('JetAirways',8,223213),
    ('thaiairways',9,4758745),
    ('airindia ',6,121),
    ('lufthansa',7,2),
    ('lufthansa',5,132),
    ('lufthansa',6,21),
    ('airindia ',1,132),
    ('scoot',3,7887)
    
    Select StockName,count(Segments) 'Segments',Sum(Amount) 'Amount' 
    From Temp 
    Group by StockName
    
    /*
    StockName                      Segments    Amount
    ------------------------------ ----------- ---------------------
    airindia                       3           121214547519.00
    goair                          1           777.00
    indigo                         1           2212.00
    JetAirways                     3           240334.00
    lufthansa                      4           5507.00
    scoot                          2           13119.00
    spice                          2           564557125442.00
    thaiairways                    2           4790866.00
    */
    



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 6:52 AM