Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 Replies


ADVERTISEMENT

Transact SQL :: XML Column Data Into Separate Column

Nov 18, 2015

I have a column with the data as below :-

<Items>
  <Item Value="Value1" />
  <Item Value="Value2" />
<Items>

How to get this data into seperate columns as 

Items
value1
value2

View 2 Replies View Related

Transact SQL :: Renaming Pivot Table Column Names Dynamically

Oct 30, 2015

I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.

I have a requirement to rename the column header where the previous month end balance period be renamed to P0.

if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.

How do I dynamically rename the column headers.

View 7 Replies View Related

Transact SQL :: Pivot And Invalid Column Name

May 13, 2015

I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.

Columns
ItemNmbr Char(31) not null
SetupTime_I Numeric(19,5) not null
WCID_I  Char(11) not null
select ItemNmbr,SetupTime_I, WCID_I from RT010130
Results

Now run
select ItemNmbr,SetupTime_I, WCID_I
from RT010130
pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT

And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.

View 5 Replies View Related

Transact SQL :: Sum Time Column After Pivot Query

May 18, 2015

i have a table like below,

CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME

[code]....

and then i pivot table by date as column wise using the below query and also displays total time by rowswise

SELECT t1.*, t2.Total
FROM (
SELECT  name,[2015-08-01],[2015-08-02]
FROM (
SELECT  name, AttDate,PresentTime 

[code]....

now what i need is to display sum of time at last row as well, means total time of against date

View 16 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

PIVOT TABLE Dynamic Column Header?

Nov 14, 2007

I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.

CREATE TABLE #owner
(
[NameId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #owner VALUES ('ME');
INSERT INTO #owner VALUES ('Other');

CREATE TABLE #propertyType
(
[TypeId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #propertyType VALUES ('Home');
INSERT INTO #propertyType VALUES ('Car');

CREATE TABLE #property
(
[NameId] tinyint NOT NULL,
[TypeId] tinyint NOT NULL,
[Value] varchar(50) NOT NULL
)

INSERT INTO #property VALUES (1,1, 'Blue');
INSERT INTO #property VALUES (1,2, 'Black');
INSERT INTO #property VALUES (2,1, 'Red');
INSERT INTO #property VALUES (2,2, 'Black');

DROP TABLE #owner;
DROP TABLE #propertyType;
DROP TABLE #property

| NameId | Name |
| 1 | ME|
| 2 | other |

| TypeId | Name |
| 1 | Home |
| 2 | Car |

| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |

Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:

[Owner | Home | Car ]
[ME | Blue | Black ]
[Other| Red | Black ]

The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.

View 9 Replies View Related

How Count Column In Pivot Table- And Add Result Row

Jan 20, 2008

how count column in pivot table- and add result row
i need to calculate each column
for example
day1 day2 day3 day4 day5
-------------------------------------------------------------------------
1 2 1 2 3
1 2 3 2 2
2 3 2 1 2
2 3 0 0 0
-----------------------------------------------------------new result row
ok ok 1|2|3 1 3

i need to check each column
if i have twice each number
if not show the missing number
TNX




Code Block
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080301', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

IF @Numdays > 28
BEGIN
SELECT p.ID,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END

View 12 Replies View Related

Column Data To Column Heading By Dynamic Pivot Maybe

Feb 27, 2008



Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41

StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41

The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.

View 9 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Summing A Column, Using A Pivot Table, Accounting For NULL

Sep 13, 2006

hello, i'm using sql200 and i am attempting to create a table that has an hourly-incrementing 'Date_Time' column, with a corresponding 'Total' column (which keeps a running total of values off of another table) . The code I am using right now is...

declare @date as smalldatetime

set @date = dateadd(yy, -1, cast(convert(char(11), current_timestamp, 101) + '00:00:00' as smalldatetime))



select dateadd(hh, i, @date) as Date_Time, sum(Subtotal) as Total

into #POGtable

from Pivot, OrderGroup

where

i between 0 and 24 and

CreationDate between @date and dateadd(hh, i, @date)

group by i





select dateadd(hh, i, @date) as Date_Time, 0 as Total

into #Ptable

from Pivot

where i between 0 and 24

group by i



select *

from #POGtable

union

select * from #Ptable p

where not exists(

select * from #POGtable pog

where p.Date_Time >= pog.Date_Time)



the solution is ugly, but the problem i'm having is that values for 'SubTotal' don't usually appear before 8 or 9 am. what you see above is me getting all the times (hours) that a subtotal present, creating another table with every possible hour in it (and with a 'Total' column as just zero), and then combining the two tables to create one flowing table over a 24-hour period.



there has GOT to be a better way to do this; the main point being that i want the sum( ) function to start adding up values immediately so i don't have to union two tables

View 3 Replies View Related

SQL Server 2014 :: Pivot Table With Column Names To Rows?

Aug 1, 2015

I have a table with following rows.

FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000

I am looking to convert it to

Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000

I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

Results:

MONTHBNQ FNB RS
Jul11054839 89032 1067299

View 4 Replies View Related

SQL Server 2012 :: Optimize PIVOT Table To Include Unlimited Column And QTY Of SKU

Jan 24, 2014

Reformatting data in a PIVOT Table or find a better way to display.

--ORDERDETAIL TABLE

SKUO   QTYO    ORDERIDO

KUM    1   12345
KUS    2   12345
SUK    1   12345
KHN    4   12345
DRE    1   12345

[Code] ....

Number of SKU's in order could be over 1000.

Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.

Data I am looking to get.  MAX of 15 SKUS Per line.

ORDERID    SKU1    QTY1    SKU2    QTY2    SKU3    QTY3    SKU4    QTY4    SKU5    QTY5    SKU6    QTY6    SKU7    QTY7    SKU8    
QTY8    SKU9    QTY9    SKU10   QTY10   SKU11   QTY11   SKU12   QTY12   SKU13   QTY13   SKU14   QTY14   SKU15   QTY15  
12345  KUM 1   KUS 2   SUK 1   KHN 4   DRE 1   HGF 2   FDE 1   CDS 1   GYT 1   POI 3   LKH 2   TTT 4   JHG 8   YUI 2   WQE 1  
12345  PMN 1   BVC 1   ABD 1  

[Code] ....

CURRENT PIVOT ONLY GOES TO 150 - BELOW

SELECT     PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10],
                      [11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],

[Code] ....

View 6 Replies View Related

Power Pivot :: DAX Formula For Extracting A Column Value From Table Based On Certain Conditions

Jul 20, 2015

I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.

Here is the scenario:

I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).

I would like to extract  Key_member where key_member_type="PM" and active as of tb1(month_end_date).

Is this possible using DAX ?

View 6 Replies View Related

Pivot Table In AdventureWorks: Invalid Column Names DepartmentID And ShiftID

Jan 3, 2008

Hi all,
I executed the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--PivotTable.sql--
USE Adventureworks

GO

SELECT ShiftID, Name

FROM HumanResources.Shift

SELECT EmployeeID, ShiftID, Name

FROM HumanResources.Employee, HumanResources.Department

WHERE Employee.DepartmentID = Department.DepartmentID

--Compute the number of employees by

--department name and shift

SELECT Name, [1] AS 'Day', [2] AS 'Evening',

[3] AS 'Night'

FROM

(SELECT e.EmployeeID, edh.ShiftID, d.Name

FROM HumanResources.Employee e

JOIN HumanResources.EmployeeDepartmentHistory edh

ON e.EmployeeID = edh.EmployeeID

JOIN HumanResources.Department d

ON edh.DepartmentID = d.DepartmentID) st

PIVOT

(

COUNT (EmployeeID)

FOR ShiftID IN

( [1], [2], [3])

) AS spvt

ORDER BY Name

--For display in book

SELECT Name, [1] AS 'Day', [2] AS 'Evening',

[3] AS 'Night'

FROM

(SELECT e.EmployeeID, edh.ShiftID, CAST(d.Name AS nvarchar(26)) 'Name'

FROM HumanResources.Employee e

JOIN HumanResources.EmployeeDepartmentHistory edh

ON e.EmployeeID = edh.EmployeeID

JOIN HumanResources.Department d

ON edh.DepartmentID = d.DepartmentID) st

PIVOT

(

COUNT (EmployeeID)

FOR ShiftID IN

( [1], [2], [3])

) AS spvt

ORDER BY Name



IF EXISTS(SELECT name FROM sys.tables WHERE name = 'pvt')

DROP TABLE pvt

GO

--Create a table that saves the result of a pivot with employee

--names instead of numbers for column values

SELECT VName, [164] 'Mikael Q Sandberg', [198] 'Arvind B Rao',

[223] 'Linda P Meisner', [231] 'Fukiko J Ogisu'

INTO pvt

FROM

(SELECT PurchaseOrderID, EmployeeID, v.Name as 'VName'

FROM Purchasing.PurchaseOrderHeader h

JOIN Purchasing.Vendor v

ON h.VendorID = v.VendorID) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) pvt

ORDER BY VName

GO

--Show an excerpt FOR VName starting with A

SELECT TOP 5 * FROM pvt

WHERE VName LIKE 'A%'

GO

--For display in book

SELECT TOP 5 CAST(VName AS NVARCHAR(22)) 'VName',

[Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt

WHERE VName LIKE 'A%'

GO

--VendorID for Advanced Bicycles is 32

--Four PurchaseOrderID column values exist in PurchaseOrderHeader

--with VendorID values of 32 and EmployeeID values of 164

SELECT VendorID, Name FROM Purchasing.Vendor WHERE Name = 'Advanced Bicycles'

SELECT PurchaseOrderID FROM Purchasing.PurchaseOrderHeader WHERE VendorID = 32 and EmployeeID = 164

--Unpivot values

SELECT TOP 8 VName, Employee, OrdCnt

FROM

(SELECT VName, [Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt) p

UNPIVOT

(OrdCnt FOR Employee IN ([Mikael Q Sandberg],

[Arvind B Rao], [Linda P Meisner], [Fukiko J Ogisu])

)AS unpvt

GO

--For display in book

SELECT TOP 8 CAST(VName AS nvarchar(28)) 'VName', CAST(Employee AS nvarchar(18)) 'Employee', OrdCnt

FROM

(SELECT VName, [Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt) p

UNPIVOT

(OrdCnt FOR Employee IN

([Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu])

)AS unpvt

GO



--Query to check unpivoted values

SELECT TOP 2 *

FROM pvt

ORDER BY VName ASC

GO

--For display in book

SELECT TOP 2 CAST(VName AS NVARCHAR(22)) 'VName',

[Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt

ORDER BY VName ASC

GO





IF EXISTS(SELECT name FROM sys.tables WHERE name = 'pvt')

DROP TABLE pvt

GO

========================================
I got the following error messages and results:

Msg 207, Level 16, State 1, Line 7

Invalid column name 'DepartmentID'.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'ShiftID'.

(86 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)

(4 row(s) affected)

(8 row(s) affected)

(8 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)

=================================================
I do not know why I got these 2 errors and how to correct them. Please help and advise me how to correct the mistakes and obtain the completely printed-out correct results.

Thanks in advance,
Scott Chang

View 3 Replies View Related

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

Transact SQL :: Order By A Column From Another Table

Sep 2, 2015

I have two tables, one is called (questions), the second one (answers).

questions columns are (ID,questionTitle)

answers columns are (ID,questionID,answer, answerDate)

I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.

View 12 Replies View Related

Transact SQL :: Get Table And Column Names From XML

Jul 6, 2015

I am trying to find the Table and column names from the below.

Is there a way i can get table name and column name from query_plan column?

SELECT TOP 100  text, query_plan,cp.plan_handle
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'

View 5 Replies View Related

Transact SQL :: Getting Table And Column Names

Jul 21, 2015

 SELECT TOP 100  text, query_plan, cp.plan_handle, qs.last_execution_time
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
  JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE objtype='Adhoc'

I have below output:

Text      Query_plan     Plan_handle     Lst_execution_time
Select id,name from person                  
<Showplan...   dshkkgdaHqrqe13232423    2015-07-21 10:50:22.713
Update customer set Cid=3 where name='abc'      
<Showplan...    poasfvrqe13232423    2015-07-21 10:16:22.500
delete from orders where  ORid=8    
<Showplan...     2ase2423     2015-07-21 10:10:22.710
Select 1,2,3,4,5 from num  
<showplan            afqfqfq   2015-07-21 10:10:22.710
 
I am looking for 

Text           Query_plan                   Plan_handle      Last_execution_time        TabName colname
Select id,name from person  <Showplan... dshkkgdaHqrqe13232423   2015-07-21 10:50:22.713  
Person Id, name Update customer set Cid=3 where name='abc'  
<Showplan   poasfvrqe13232423   2015-07-21 10:16:22.500  customer  Cid,  name
delete from orders where  ORid=8   <Showplan...   2ase2423     2015-07-21 06:10:22.710    Orders    ORid
Select 1,2,3,4,5 from num <showplan            afqfqfq   2015-07-21 10:10:22.710        nUM      1,2,3,4,5

View 2 Replies View Related

Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?

Jun 29, 2015

I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).

Table1 columns are: table1Id; Name.  Table2 columns include Table2.table1Id which is the foreign key to Table1.

What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?

View 11 Replies View Related

How Do I Separate 1 Column Into 2 Or More Column?

Dec 20, 2006

I have the following result in my query:
PlanName1 :08/22/2006 - 11/01/2006; PlanName2:03/13/2006 - 08/21/2006;
How do I separate 1 column into 2 or more column?

View 1 Replies View Related

Transact SQL :: Column Length Restriction When Naming CTE Column?

Jun 22, 2015

My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???

View 2 Replies View Related

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

Jul 17, 2015

I have a SQL Query issue you can find in SQL Fiddle

SQL FIDDLE for Demo

My query was like this

For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1

I supposed to get last row as 

    cc  Zz  bab

Instead I am getting 

  cc  Zz  rrr 

which is wrong

View 8 Replies View Related

Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?

Nov 4, 2015

#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):

View 6 Replies View Related

Transact SQL :: Calculate SUM Of 100 Of EDSUM Column For EDCOST Column

Aug 18, 2015

How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.

Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2))
Insert into #sum values (121, 2000,'HLMO',98,'DT')
Insert into #sum values (122, 2000,'HLMT',2,'DT')
Insert into #sum values (123, 2001,'HLMO',100,'DT')
Insert into #sum values (124, 2002,'HLMD',97,'DT')

[Code] ...

Expeced Output:
ED EDCOST EDName EDSum EDCode
126 2003 HLMR   98 DT
130 2005 HLMR   98 DT

View 7 Replies View Related

Separate A Column Into Many

Jul 19, 2007

Hi

I have a column say “Col_1� in which “=>� is used as separator.
Col_1 data is as follows
House => Street => Area => City => Country =>

I want to create separate columns for House, Street, Area etc from Col_1 using “=>’ separator.

Please advise how?

Thanks
Jawad

View 1 Replies View Related

Transact SQL :: Create Table With Timestamp Column

Jul 2, 2015

Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.

View 3 Replies View Related

Transact SQL :: Column Update With Comparison With Another Table

Nov 10, 2015

I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.

Txt_Temp - Table

I tried this code with no succes and only one row is updating

update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber

OR Add this code in view 

Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code 
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber

Vin
123456
123123
123789

Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465

My Result can be in Txt_Temp table or new table or with one or two columns

asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789

View 10 Replies View Related

Transact SQL :: Display Different Table Having Same Column With Different Data

Sep 25, 2015

I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e  tbl_NewEmployee having 6 record  and tbl_oldEmployee having 10 record,so i need to display data from  tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).

View 3 Replies View Related

Transact SQL :: Table With SPARSE Column Need More Space?

Jul 24, 2015

As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:

/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)

I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb

SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only  466,031MB !

That means the Table with SPARSE column need more storage, Why? 

By the way, in table Sprstb column address has  1666198  Null mark (from 5000000)

View 5 Replies View Related

Transact SQL :: XML Data Column - Insert Into Another Table

May 29, 2015

I have table with about 10000 rows,  there is a column named  Raw_XMLData is defined as varchar but data is xml format.  

I try to insert into another table, if Raw_XMLData column has is valid xml data?

Is it possible to do in T sql?

View 2 Replies View Related

Separate String To Three Column

Mar 2, 2015

How to separate column FullName to three column LastName, FirstName, and MI? Sample of FullName - Smith, John P.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved