Home
Search results “Pivot for select oracle”
How to use the Oracle SQL PIVOT Clause
 
11:46
PIVOT allows you to "flip a table on its side", i.e. Columns to Rows / Rows to Columns. Previously we did this with DECODE or CASE. As of Oracle 11g, Oracle Database includes the PIVOT clause (and UNPIVOT). Oracle expert Geoff Wiland from SkillBuilders will demonstrate PIVOT, UNPIVOT, including the use of aggregate functions.
Views: 11023 SkillBuilders
Oracle sql Interview Question : How to change rows to column
 
09:18
This video tutorial talks about the frequently asked oracle sql interview question of changing rows to the column. Based on the given scenario, we are asked to change the presentation of data in a table with 3 rows and 2 column to 3 columns and 2 rows. Decode Function in oracle https://youtu.be/YUAjPMjqMws Pivot and unpivot in oracle https://youtu.be/6s0DI1YcWIM
Views: 16919 Kishan Mashru
PIVOT Tutorial - generating dynamic column with PIVOT Interview Question SQL
 
14:14
In this lecture you will learn about PIVOT operator and using it to generate dynamic columns.PIVOT is very use full operator to convert values into columns. PIVOT: •Convert your output from (values)vertical order to horizontal(making columns) one Topic to cover: •PIVOT example •PIVOT syntax •Interview Question •PIVOT on northwind •Dynamic PIVOT query http://www.techsapphire.in/index/pivot_sql_lesson_with_generating_dynamic_columns_with_pivot/0-158
Views: 30690 techsapphire
Função Oracle PIVOT
 
08:24
Acesse o desafio: http://novidades.aprendaplsql.com/15-dias-de-funcoes-oracle A função PIVOT cria uma matriz de informações conforme as necessidades do usuário. SELECT e.manager_id, e.department_id, count(e.employee_id) FROM hr.employees e GROUP BY e.manager_id, e.department_id ORDER BY e.manager_id, e.department_id; SELECT * FROM (SELECT manager_id, department_id, employee_id FROM hr.employees ) PIVOT ( COUNT(employee_id) FOR department_id IN (10, 20, 30, 50, 60, 80, 90) ) ORDER BY manager_id SELECT e.manager_id, d.department_name, count(e.employee_id) FROM hr.employees e INNER JOIN hr.departments d ON e.department_id = d.department_id GROUP BY e.manager_id, d.department_name ORDER BY e.manager_id, d.department_name; WITH valores_pivot AS ( SELECT e.manager_id manager_id, d.department_name department_name, e.employee_id qtd_empregados FROM hr.employees e INNER JOIN hr.departments d ON e.department_id = d.department_id ) SELECT * FROM valores_pivot PIVOT ( COUNT(qtd_empregados) FOR department_name IN ('IT', 'Administration', 'Executive', 'Marketing', 'Purchasing', 'Sales', 'Shipping') ) ORDER BY manager_id; Funções Oracle: https://www.youtube.com/playlist?list=PLMaqczuL07LkjrX6Rvx6TdNM-1nbUKG8p
Views: 2432 Aprenda PL/SQL
Dynamically Pivot Data in SQL Server
 
13:50
In this video we will write T-SQL in SQL Server to build a dynamic query that will pivot a data set. Watch the video and get the code here: https://anthonysmoak.com/2018/08/05/how-to-dynamically-pivot-data-in-sql-server/
Views: 2404 Anthony B. Smoak
Connect Excel to Oracle Database
 
05:03
How to connect an Oracle Database to Microsoft Excel. https://www.elance.com/s/edtemb/
Views: 102554 i_marketing
Part 6   Transform rows into columns in sql server
 
08:53
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/06/part-6-transform-rows-into-columns-in.html This is another common sql server interview question. We will be using Countries table in this example. SQL to create the table Create Table Countries ( Country nvarchar(50), City nvarchar(50) ) GO Insert into Countries values ('USA','New York') Insert into Countries values ('USA','Houston') Insert into Countries values ('USA','Dallas') Insert into Countries values ('India','Hyderabad') Insert into Countries values ('India','Bangalore') Insert into Countries values ('India','New Delhi') Insert into Countries values ('UK','London') Insert into Countries values ('UK','Birmingham') Insert into Countries values ('UK','Manchester') Here is the interview question. Write a sql query to transpose rows to columns. Using PIVOT operator we can very easily transform rows to columns. Select Country, City1, City2, City3 From ( Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence from Countries ) Temp pivot ( max(City) for ColumnSequence in (City1, City2, City3) ) Piv
Views: 199146 kudvenkat
How to Parse XML in Oracle
 
13:54
This Video Shows 1. How to Read and parse XML in Oracle using EXTRACTVALUE and XMLTABLE. 2. When to use extractvalue and XMLTable. 3. Brief Introduction to XMLPATH
Views: 13341 yrrhelp
How to transpose column to row in sql
 
12:43
Hi friends today we will learn how to transpose column data into row of table. #transposecolumntorow Oracle database Unbeatable,Unbreakable Platform..
Views: 16855 Oracle World
SQL Server Programming Part 18 - Dynamic Pivot Tables
 
13:08
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - The most annoying part of creating a pivot table in Microsoft SQL Server is listing out the column headings for the table. This video explains how to use dynamic SQL so that you no longer have to repeat this laborious task. The video includes a quick recap of basic pivot tables, how to use the QuoteName function to generate a list of column headings, how to build an SQL statement and store it as a string of text and, finally, how to execute the dynamic SQL string. You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip You can view a written version of this tutorial at: https://www.wiseowl.co.uk/blog/s417/pivoting-sql-data.htm You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 44132 WiseOwlTutorials
Pivot Table and its advanced properties in OBIEE 11g and 12C
 
08:22
This video shows you advanced properties of pivot table in obiee 11g and obiee 12c
SQL SELECT 15 - PIVOT & CASE by Kalman Toth
 
11:18
SQL SELECT 15 is the fifteenth video of 16 tutorial videos. It presents Crosstab Queries Using PIVOT & CASE. SQL Server 2014 Database Design paperback/kindle by Kalman Toth is available on Amazon ( http://goo.gl/gEIrIO ).
Views: 376 SQLUSA
Oracle SQL MERGE Statement
 
07:52
https://www.databasestar.com/oracle-merge-sql/ In this video, we’ll learn what the Oracle MERGE statement does and how you can use it. What is the MERGE statement? It’s a statement that lets you either insert or update data in a table - all in a single statement! It’s helpful if you’re using an ETL (Extract Transform Load) process when creating or updating a data warehouse. It’s also helpful when loading data into your own database if you’re not creating a data warehouse. It allows you to prevent duplicate data and ensure you have up-to-date data in your table. The syntax looks like this: MERGE INTO table_name USING table_name ON (condition) WHEN MATCHED THEN update_clause DELETE where_clause WHEN NOT MATCHED THEN insert_clause [LOG ERRORS log_errors_clause reject_limit integer | unlimited ] In this video, I also show an Oracle SQL MERGE statement example, so watch the video to find out more. For more information on the MERGE statement and how you can use it in your queries, read the related article here: https://www.databasestar.com/oracle-merge-sql/
Views: 910 Database Star
Calculated Fields using values in other rows
 
05:07
Another little tip on Oracle BI based on a question I once got asked How to use formulas in calculated fields to compare values from different rows, using a function called MSUMS
Views: 3686 Edward's Oracle Blog
Pivot in sql server 2008   Part 54
 
12:25
In this video we will learn about pivot operator. Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/10/pivot-operator-in-sql-server-part-54.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-54-pivot-in-sql-server-2008.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 190070 kudvenkat
Pivot tables in SQL
 
06:30
Views: 339 Avinash avi
How To Transpose Columns To Rows In Oracle
 
05:09
Columns To Rows In Oracle
Views: 345 Adam Tech
How to use Pivot and Unpivot? (how to change rows to column and column to rows)
 
10:47
This is a video tutorial on using pivot and unpivot operators to change rows to column and columns to rows when needed, the video also demonstrates a way to change from rows to column without using the pivot operator which can be helpful if you are using and older version of oracle or some other database without the feature of pivot and unpivot. Both the operators are explained with an example for better understanding
Views: 7892 Kishan Mashru
PL/SQL tutorial 78: PL/SQL FORALL statement with INDICES OF clause in Oracle Database
 
09:19
Checkout this latest PL/SQL tutorial on RebellionRider.com and learn what are the shortcomings of first bound clause (i.e. Lower & Upper Bound) and how we can overcome those shortcomings using INDICES OF bound clause of FORALL statement in Oracle PL/SQL by Manish Sharma. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/forall-3 Bolg is online but you can still learn what is a DBMS http://bit.ly/the-DBMS Previous Tutorial ► FORALL with Lower & Upper Bound https://youtu.be/z5-hSxyUrkk ►PL/SQL FOR LOOP https://youtu.be/DfAmnj2j7WI ►SELECT-INTO statement https://youtu.be/F5eMJhwmCQs ►Nested Table https://youtu.be/EUYyiFCzU0I ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 5369 Manish Sharma
UNPIVOT in SQL Server
 
04:33
sql server convert columns to rows in a table sql server transpose columns to rows unpivot in sql server example sql server unpivot example In this video we will discuss UNPIVOT operator in SQL Server. PIVOT operator turns ROWS into COLUMNS, where as UNPIVOT turns COLUMNS into ROWS. We dicussed PIVOT operator in Part 54 of SQL Server tutorial. Please watch Part 54 before proceeding. http://csharp-video-tutorials.blogspot.com/2012/10/pivot-operator-in-sql-server-part-54.html Let us understand UNPIVOT with an example. We will use the following tblProductSales table in this demo. SQL Script to create tblProductSales table Create Table tblProductSales ( SalesAgent nvarchar(50), India int, US int, UK int ) Go Insert into tblProductSales values ('David', 960, 520, 360) Insert into tblProductSales values ('John', 970, 540, 800) Go Write a query to turn COLUMNS into ROWS. SELECT SalesAgent, Country, SalesAmount FROM tblProductSales UNPIVOT ( SalesAmount FOR Country IN (India, US ,UK) ) AS UnpivotExample Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/unpivot-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/unpivot-in-sql-server_10.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 46922 kudvenkat
Creating Calculated Items Using Oracle BI EE
 
02:26
This video teaches you how to create a calculated item for a specific set of data values. A calculated item is a computation between members. To find out more about Oracle BI EE, see the documentation at https://docs.oracle.com/en/middleware Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
SQL 2008 series: SQL Pivot / SQL Dynamic Query
 
06:33
http://www.creativecommit.com This demo gives a brief overview of SQL pivot and constructing dynamic queries
Views: 10341 creativecommIT
Adding Views for Charts, Pivot Tables, and Filters Using Oracle BI EE
 
03:08
This video teaches you how to add a chart (by using Recommended Visualization), pivot table, and filter to an existing analysis. To find out more about Oracle BI EE, see the documentation at https://docs.oracle.com/en/middleware Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
How to return multiple values from a function in oracle pl/sql ? (without using out parameter)
 
14:19
This video demonstrates how a function can return multiple values to the calling environment, but without using multiple out parameters, the video shows an example on how we can create a object, a nested table based on the object and then returning the nested table type from the function.
Views: 20409 Kishan Mashru
TSQL: Building Pivot Tables
 
09:16
An example of how to build a Pivot table and how they can be useful.
Views: 12413 SQLInSix Minutes
SQL Server  Generating Cross Tab Tables (Pivot)
 
07:39
6. Generating Cross Tab Tables (Pivot)
Views: 17889 Eagle
Merge Statement in Oracle SQL with Example (UPDATE/DELETE or INSERT)
 
15:04
This video demonstrates how to write a Merge Statement in oracle sql, how it is useful in doing a conditional update or delete or both of them when the condition between the target table and source table is evaluated to true, and how we can insert the record into the target table when condition is evaluated to false. The video also shows the use of UPDATE, DELETE and INSERT with Merge, and also the various possible ways to use a Merge statement!!!
Views: 9574 Kishan Mashru
PIVOT Clause
 
05:52
Views: 31 Geeky Abhijeet
Adding Pivot Tables and Action Links to an Analysis in Oracle Fusion HCM Cloud
 
06:49
In this tutorial you learn how to add pivot tables and action links to an analysis in that shows absence data by business unit. The example in this video is from HCM but applies to all applications that use OTBi and OTBI Enterprise including Oracle Financials Cloud, Oracle Project Portfolio Management Cloud, and Oracle Procurement Cloud. Please note that all of the techniques and concepts in this tutorial also apply to OTBI Enterprise although the available subject areas will be different.
Views: 7062 Oracle EPM Tutorials
Microsoft SQL Server - Pivot
 
05:29
Microsoft SQL Server,Pivot,SQL Server Tutorial,T-SQL,Oracle
Views: 922 Hima Bindu
How to configure Microsoft Excel to connect to an Oracle Database
 
11:52
Mike van der Velden, Product Manager at Simba Technologies, provides a step-by-step guide for configuring Microsoft Excel to connect with Oracle Database OLAP Option. Mike walks through the one-time set-up of Oracle's Transparent Networking Substrate (TNS), followed by the Data Source Name (DSN) and Office Data Connection (.odc). Once configured, Microsoft Excel connects easily to Oracle Database OLAP Option for ad hoc data query, data analysis and BI reporting.
How to Export data from Oracle to Excel
 
05:48
This tutorial shows you how to export data from Oracle to excel sheet. sql developer is used in this tutorial to export the data with two methods. Please feel free to comment. Please subscribe for more
SQL Dynamic Pivot Example Adventure Works
 
05:16
TSQL Dynamic Pivot example in adventure works. Pivot Example: https://www.youtube.com/watch?v=HmXv76kh78M Concatenate all product ids: https://www.youtube.com/watch?v=1kfp0s-lc3M
Views: 1757 Blake B
How To Transform MySQL Rows Into Column And Become A Pivot Table Like OLAP Dimension
 
09:23
In this video you will learn on How To Transform MySQL Rows Into Column And Become A Pivot Table Like an OLAP Dimension. It can reduce so much time especially if you have a huge data. Di dalam video ini saya akan menjelaskan tentang bagaimana cara merubah record MySQL menjadi kolom seperti pivot table melalui query tanpa menggunakan OLAP atau analysis service. Convert MySQL Rows, Convert MySQL Rows Into Column, Transform MySQL Rows, Merubah record menjadi kolom, Merubah baris menjadi kolom
Views: 7585 Yadishare Tutorial
SQL Pivot Example Adventure Works
 
12:20
SQL Pivot example in adventure works.
Views: 689 Blake B
Correlated subquery in sql   Part 60
 
07:59
In this video we will discuss about Corelated Subqueries In Part 59, we discussed about 2 examples that uses subqueries. Please watch Part 59, before proceeding with this video. We will be using the same tables and queries from Part 59. In the example below, sub query is executed first and only once. The sub query resutls are then used by the outer query. In a non-corelated subquery, the subquery can be executed independently of the outer query. Select [Id], [Name], [Description] from tblProducts where Id not in (Select Distinct ProductId from tblProductSales) If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery. In the where clause of the subquery below, "ProductId" column get it's value from tblProducts table that is present in the outer query. So, here the subquery is dependent on the outer query for it's value, hence this subquery is a correlated subquery. Correlated subqueries get executed, once for every row that is selected by the outer query. corelated subquery, cannot be executed independently of the outer query. Select [Name], (Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity from tblProducts order by Name Text version of the video http://csharp-video-tutorials.blogspot.com/2013/01/correlated-subquery-in-sql-part-60.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-60-co-related-subqueries.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 131324 kudvenkat
Intermediate To Advanced SQL Queries #2: Pivoting Tables
 
19:54
In this tutorial, we will cover a special case of pivoting tables in SQL using MySQL as a DBMS. Think of pivoting tables as selecting unique values and assigning them to their own columns. The solution code can be found on my website here: http://programmingforfinance.com/2017/10/pivoting-tables-with-mysql/
Views: 3426 codebliss
Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records
 
18:02
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps: 1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal. 2. (02:00) Import and transform Data Set, including removing Columns, Filter For Records and adding an extra Column to calculate Net Revenue using the Number.Round Power Query Function. 3. (06:38) Load Report to Sheet 4. (07:09) Import each Parameter/Criteria Table. 5. (07:36) Convert each Criteria Table to a single Text Item using the Record.Field Power Query Function. 6. (11:38) Declare and Insert Variables into M Code for Report 7. (14:10) Test Reporting System 8. (14:30) Create Recorded Macro To Refresh All and assign it to a Form button 9. (16:40) Test Macro and Reporting System 10. (17:25) Summary
Views: 79581 ExcelIsFun
Oracle SQL TUTORIAL – IF condition using CASE and DECODE  - An Example
 
09:31
In this video, we shall see how case and decode is used in real time scenario. SQL used in the video is given below. SELECT * FROM emp_test ORDER BY gender; count_of_employees Count_of_Male_Employees Count OF Female Employees -- 15 5 8 select count(*) " Total Employees" from emp_test; select gender,count(*) " Total Employees" from emp_test group by gender; select sum(decode(gender,'M',1,0)) Count_OF_Male_Employees from emp_test; select sum(case gender when 'F' then 1 else 0 end) as Count_OF_FeMale_Employees from emp_test; SELECT count(*) " Total Employees" , sum(decode(gender,'M',1,0)) Count_OF_Male_Employees, sum(case gender when 'F' then 1 else 0 end) Count_OF_Female_Employees from emp_test;
Views: 105 Ganesh Anbarasu
Power BI direct connect to SQL database (7-3)
 
04:40
This video is part of the Analyzing and Visualizing Data with Power BI course available on EdX. To sign up for the course, visit: http://aka.ms/pbicourse. To read more: Power BI service https://aka.ms/pbis_gs Power BI Desktop https://aka.ms/pbid_gs Power BI basic concepts tutorial: https://aka.ms/power-bi-tutorial To submit questions and comments about Power BI, please visit community.powerbi.com.
Views: 45866 Microsoft Power BI
Part 4   Delete duplicate rows in sql
 
03:26
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-4-delete-duplicate-rows-in-sql.html In this video, we will discuss deleting all duplicate rows except one from a sql server table. SQL Script to create Employees table Create table Employees ( ID int, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000) Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) The delete query should delete all duplicate rows except one. Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions. WITH EmployeesCTE AS ( SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber FROM Employees ) DELETE FROM EmployeesCTE WHERE RowNumber ] 1
Views: 349292 kudvenkat
The SUM Function (Introduction to Oracle SQL)
 
03:26
Learn how to add values using the SUM function. The full Introduction to Oracle SQL course is available here: https://www.databasestar.com/introduction-to-oracle-sql-course/
Views: 157 Database Star
Dynamic sql table name variable
 
11:59
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable_20.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss how to pass table name dynamically for stored procedure in sql server. This is one of the sql questions that is very commonly asked. I have a web page with a textbox. When I enter a table name in the textbox and when I click "Load Data" button, we want to retrieve data from that respective table and display it on the page. Copy the SQL Script to create the tables from my blog using the link below http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html Create the following stored procedure. Notice we are passing table name as a parameter to the stored prcoedure. In the body of the stored procedure we are concatenating strings to build our dynamic sql statement. In our previous videos we discussed that this open doors for SQL injection. Create procedure spDynamicTableName @TableName nvarchar(100) As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from ' + @TableName Execute sp_executesql @sql End So the obvious question that comes to our mind is, why are we not creating parameterised sql statement instead. The answers is we can't. SQL Server does not allow table names and column names to be passed as parameters. Notice in the example below, we are creating a parameterised query with @TabName as a parameter. When we execute the following code, the procedure gets created successfully. Create procedure spDynamicTableName1 @TableName nvarchar(100) As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from @TabName' Execute sp_executesql @sql, N'@TabName nvarchar(100)', @TabName = @TableName End But when we try to execute it we get an error - Must declare the table variable "@TabName" Execute spDynamicTableName1 N'Countries' Add a Web Page to the project that we have been working with in our previous video. Name it "DynamicTableName.aspx". Copy and paste the HTML from my blog using the link below http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html Copy and paste the code from my blog in the code-behind page http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html At this point, run the application and type the following text in the "Table Name" textbox and click "Load Data" button. Notice "SalesDB" database is dropped. Our application is prone to SQL injection as we have implemented dynamic sql in our stored procedure by concatenating strings instead of using parameters. Employees; Drop database SalesDB One way to prevent SQL injection in this case is by using SQL Server built-in function - QUOTENAME(). We will discuss QUOTENAME() function in detail in our next video. For now understand that by default, this function wraps that string that is passed to it in a pair of brackets. SELECT QUOTENAME('Employees') returns [Employees] Modify the stored procedure to use QUOTENAME() function as shown below. Alter procedure spDynamicTableName @TableName nvarchar(100) As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from ' + QUOTENAME(@TableName) Execute sp_executesql @sql End At this point, type the following text in the "Table Name" textbox and click "Load Data" button. Notice you will see a message - Invalid object name 'Employees; Drop database SalesDB'. Also "SalesDB" database is not dropped. Employees; Drop database SalesDB The entire text in "Table Name" textbox is wrapped in a pair of brackets by the QUOTENAME function and is treated as table name. Since we do have a table with the specified name, we get the error - Invalid object name.
Views: 26825 kudvenkat
pivot ve unpivot
 
12:21
Bu videoda sql server da pivot ve unpivot sorgulama çeşidi anlatılmıştır
Views: 123 Havva yuksel
connect Excel with Oracle Database
 
03:31
In this video, we are going to teach you about "connect Excel with Oracle Database" Below are the steps followed. 1.Open Excel. 2.open "Data sources" on Excel Menu 3.Create required datasoure. 4.Give the required details(Service, Username, Password) on the utility displayed on Excel 5.Click on finish 6.Now select the table and optimize query to fetch the data from database(Here we have considered Oracle database) 7.Finish.to come back to Excel sheet 8.Now we can see the data in the Excel sheet.Manipulate the data in Excel accordingly. Please subscribe to our channel for more videos....
Views: 5956 WebTechs
SQL Pivot, a working copy-paste example
 
02:41
/* Why Pivot? Pivoting is just showing aggregate data sideways. It just the same data in another format, another angle of the data. */ -- Generate sample data, Hotel rooms and prices DECLARE @t TABLE (RoomType varchar(20), Price money, RoomNumber int) INSERT INTO @t(RoomType, Price, RoomNumber) VALUES ( 'SingleRoom' ,100.00, 300) , ( 'SingleRoom' , 92.00, 301 ), ( 'DoubleRoom', 140.00, 316), ( 'DoubleRoom', 120.00, 317), ( 'DoubleRoom', 180.00, 318), ( 'Suite', 500.00, 900) --------Example 1: Basic pivoting---------------------------------------------------------- SELECT 'Average Price:' as Pivoted, SingleRoom,DoubleRoom --,Suite /* 1] Choice from Rows to pivot in section 4 */ FROM ( SELECT RoomType, Price /* 2]: The Columns needed */ FROM @t ) AS ColumnsNeeded PIVOT( AVG(Price) /*3] Aggregate Function */ For RoomType in ([SingleRoom], [DoubleRoom], Suite) /* 4], Rows to pivot */ ) AS RowsToPivot; -- Regular aggregate query--------------------------------------------------------------------- SELECT RoomType, avg(Price) as AveragePrice FROM @T GROUP BY Roomtype go ---------- Example 2, see multiple pivoted aggregates DECLARE @t TABLE (RoomType varchar(20), Price money, RoomNumber int) INSERT INTO @t(RoomType, Price, RoomNumber) VALUES ( 'SingleRoom' ,100.00, 300) , ( 'SingleRoom' ,92.00, 301 ), ( 'DoubleRoom', 140.00, 316), ( 'DoubleRoom', 120.00, 317), ( 'DoubleRoom', 180.00, 318), ( 'Suite', 500.00, 900) -- Pivoted: Rows become columns. You may choose a small part or ALL rows SELECT 'Best Price:' as HotelRate, SingleRoom, DoubleRoom ,Suite FROM ( SELECT RoomType, Price FROM @t ) as ColumnsNeeded PIVOT( min(Price) /* Aggregate Function */ For RoomType in ([SingleRoom],[DoubleRoom],Suite /* Selected Rows */ ) ) as RowsToPivot UNION ALL SELECT 'Average Price:' as Pivoted,SingleRoom,DoubleRoom ,Suite /* [1] Choose rows which become columns */ FROM ( SELECT RoomType, Price /* 2: The Columns needed */ FROM @t ) AS ColumnsNeeded PIVOT( AVG(Price) /*3 Aggregate Function */ For RoomType in ([SingleRoom], [DoubleRoom], Suite /* 4, Rows needed */ ) ) AS RowsToPivot UNION ALL SELECT 'Max Price:' as Pivoted,SingleRoom,DoubleRoom ,Suite /* [1] Choose rows which become columns */ FROM ( SELECT RoomType, Price /* 2: The Columns needed */ FROM @t ) AS ColumnsNeeded PIVOT( Max(Price) /*3 Aggregate Function */ For RoomType in ([SingleRoom], [DoubleRoom], Suite /* 4, Rows needed */ ) ) AS RowsToPivot
Views: 578 Coconut SQL
PL/SQL tutorial 79: PL/SQL FORALL statement with VALUES OF clause in Oracle Database
 
10:29
RebellionRider.com presents the latest PL/SQL tutorial on FORALL statement with VALUES OF Bound clause in Oracle Database by Manish Sharma ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/forall-4 learn what is a Database management system http://bit.ly/the-DBMS Previous Tutorial ► FORALL with INDICES OF https://youtu.be/N2pv9wyTDqM ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 4548 Manish Sharma
Row Number function in SQL Server
 
07:24
sql server row_number example sql server row number by partition sql server row_number over partition by order by In this video we will discuss Row_Number function in SQL Server. This is continuation to Part 108. Please watch Part 108 from SQL Server tutorial before proceeding. Row_Number function Introduced in SQL Server 2005 Returns the sequential number of a row starting at 1 ORDER BY clause is required PARTITION BY clause is optional When the data is partitioned, row number is reset to 1 when the partition changes Syntax : ROW_NUMBER() OVER (ORDER BY Col1, Col2) Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause. SELECT Name, Gender, Salary, ROW_NUMBER() OVER (ORDER BY Gender) AS RowNumber FROM Employees Please note : If ORDER BY clause is not specified you will get the following error The function 'ROW_NUMBER' must have an OVER clause with ORDER BY Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1. SELECT Name, Gender, Salary, ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Gender) AS RowNumber FROM Employees Use case for Row_Number function : Deleting all duplicate rows except one from a sql server table. Discussed in detail in Part 4 of SQL Server Interview Questions and Answers video series. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server_30.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 85380 kudvenkat