logo
down
shadow

How to show as column to recursive rows in a table With SQL Server


How to show as column to recursive rows in a table With SQL Server

Content Index :

How to show as column to recursive rows in a table With SQL Server
Tag : sql , By : ThF
Date : November 24 2020, 01:01 AM

I hope this helps you . We have a table like this: , You need to use pivot to do this.
SELECT patientId,[code1],[code2]
FROM  (SELECT *,
              'code'+ CONVERT(VARCHAR(20), Row_number() OVER(partition BY patientId ORDER BY (select null) )) piv_col
       FROM   Tablename) a
      PIVOT (Max(Code)
            FOR piv_col IN ([code1],
                            [code2])) piv 

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Apply a recursive CTE on grouped table rows (SQL server 2005)


Tag : sql-server-2005 , By : iyogee
Date : March 29 2020, 07:55 AM
should help you out Some things to try
Instead of joining with every row and filtering the results in your where clause, could you try if reducing the amount of records directly in the join speeds things up? Add a covering index on PersonKey, RoomKey, CheckOut & Row and see if it improves performance.
;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW)
as (select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU
    where RU.ROW = 1

    union all

    select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU 
         inner join CTE on CTE.ROW + 1 = RU.ROW
                           and CTE.CHECKIN = RU.CHECKOUT
                           and CTE.PERSONKEY = RU.PERSONKEY
                           and CTE.ROOMKEY = RU.ROOMKEY
)

Display distinct rows of a table with the sum of a column of all duplicate rows in SQL Server 2008


Tag : sql-server-2008 , By : Govind Bhavan
Date : March 29 2020, 07:55 AM
With these it helps There are two tables : , You could try below ways
Method1:
select taskname,
userid,
min(startdate) as'first occurence',
max(enddate) as'last occurence'
,sum(hours)
 from t1
 group by taskname,userid
 select 
 distinct taskname,userid,b.*
 from t1
 cross apply
 (select min(startdate) as Firstoccur,max(startdate) as secondocc,sum(hours) as hrs
 from t1 t2 where t1.taskname =t2.taskname and t1.userid=t2.userid
 group by t2.taskname,t2.userid) b
 with cte
 as
 (
 select taskname,userid,
 min(startdate) over (partition by taskname,userid) as 'first',
 max(enddate) over (partition by taskname,userid) as 'second',
 sum(hours) over (partition by taskname,userid) as 'hrs',
 ROW_NUMBER() over (partition by taskname,userid order by taskname,userid) as rn
   from t1
   )
   select *from cte where rn=1

SQL Server: How substract rows of one column from rows of another table's column


Tag : sql-server , By : Lars
Date : March 29 2020, 07:55 AM
it should still fix some issue I have two tables A and B: , Use NOT EXISTS predicate
SELECT * FROM B WHERE NOT EXISTS (SELECT 1 FROM A WHERE A.ID = B.ID)

In SQL Server, how to show table rows value in column?


Tag : sql , By : chawei
Date : March 29 2020, 07:55 AM
I wish this help you I'm working with SQL Server 2012, my table looks like this:
    CREATE TABLE #Table1
    ([Customer] int, [Status] varchar(10), [StatusType] varchar(13))
;

INSERT INTO #Table1
    ([Customer], [Status], [StatusType])
VALUES
    (3, 'Ok', 'personalInfo'),
    (3, 'Pending', 'FinancialInfo'),
    (3, 'NeedUpdate', 'CompanyInfo')      
SELECT * 
    FROM #Table1
    PIVOT ( max([status])
      for [StatusType] in ([PersonalInfo], [FinancialInfo], [CompanyInfo])) AS pvt
Customer    PersonalInfo    FinancialInfo   CompanyInfo
3             Ok             Pending         NeedUpdate
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[StatusType]) 
            FROM #Table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT customer, ' + @cols + ' from 

                #Table1
    PIVOT ( max([status])
      for [StatusType] in ([PersonalInfo], [FinancialInfo], [CompanyInfo])) AS pvt'


exec(@query)

How to show table result column and show it on rows


Tag : php , By : LinnheCreative
Date : March 29 2020, 07:55 AM
this will help Can someone help me create a table in where I can use the other rows as columns? , I think you just want a pivot query:
SELECT
    NAME,
    DATE,
    MAX(CASE WHEN TYPE = 'AM IN'  THEN TIME END) AS AM_IN,
    MAX(CASE WHEN TYPE = 'AM OUT' THEN TIME END) AS AM_OUT,
    MAX(CASE WHEN TYPE = 'PM IN'  THEN TIME END) AS PM_IN,
    MAX(CASE WHEN TYPE = 'PM OUT' THEN TIME END) AS PM_OUT
FROM yourTable
GROUP BY
    NAME, DATE;
Related Posts Related QUESTIONS :
  • How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?
  • How to pass a comma separated list to a stored procedure?
  • Multiple foreign keys?
  • Inheritance in database?
  • What point should someone decide to switch Database Systems
  • SQL Case Expression Syntax?
  • Drop all tables whose names begin with a certain string
  • What program can I use to generate diagrams of SQL view/table structure?
  • Suggestions for implementing audit tables in SQL Server?
  • Distribution of table in time
  • Backup SQL Schema Only?
  • SQL query for a database scheme
  • Timer-based event triggers
  • SQL query, count and group by
  • Paging SQL Server 2005 Results
  • SQL Server 2005 For XML Explicit - Need help formatting
  • How do I use T-SQL Group By
  • How do I split a string so I can access item x?
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com