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
ADVERTISEMENT
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
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
Nov 4, 2015
Is it possible to pivot the output data into multiple rows?
I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.
View 9 Replies
View Related
Jul 1, 2014
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'
SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],[1],[2],
[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[Code] ....
View 9 Replies
View Related
Jul 8, 2014
I have created a table name "tblPopulation" with fields 'Country', 'State' etc....
When i query it with table name as tblpopulation with fields 'country', 'state' then i get error as invalid object name.
View 9 Replies
View Related
Dec 18, 2014
I need to convert the column to rows but within group. example
Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11
This need to be transposed to :
Group a b c d
p1234
q56NULL7
r891011
View 3 Replies
View Related
Oct 13, 2015
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
View 3 Replies
View Related
Jan 23, 2015
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
UNION
SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".
View 2 Replies
View Related
Jan 7, 2015
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
View 2 Replies
View Related
Sep 9, 2015
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
View 6 Replies
View Related
Dec 18, 2014
I am looking for SQL query which uses 2 tables CASH and BALANCE.
eg: Need Tablename, ColumsList and data in the results set.
eg: 10 rows shown below and ordered based on Acct_number
Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE
Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY
Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row6,BALANCE,10,500,750,DR,12/12/2014,USD
Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row10,BALANCE,20,500,750,DR,12/12/2014,USD
View 7 Replies
View Related
Sep 10, 2015
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,
[Code]....
View 4 Replies
View Related
Mar 21, 2015
Select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor
View 3 Replies
View Related
Jul 5, 2015
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
View 3 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Feb 3, 2015
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
View 3 Replies
View Related
May 5, 2015
My DB contains company names repeating themselves several times (in the same column).
Can I create a new table that shows only the distinct names, and use it to work with the data?
My intention is to allow my users to choose only from the options within the DB (mimic in a way the list validation option in excel)
View 6 Replies
View Related
Jul 23, 2005
I am struggling on this issue and was hoping if anyone out there canhelp me.Here is the setup: I have a table with the following data:TableName: TranDetailMerchID ItemName Price------------------101 A 5101 B 3.5101 C 0102 B 7.6102 C 4102 E 65102 G 4103 K 35Table DesignMerchID intItemName varchar(50)Price floatWhat I would like is a report that looks like thisMerchID A B C E G K101 5 3.5 0102 7.6 4 65 4103 35This report can change on every run depending on data in tableTranDetail. The column name in report depends on ItemName in tableTranDetail. As seen in the above report, there is no data for Items D,F, H, I, J and hence they do not show up in the report.What I need: Code for a stored procedure that can get me this data.Thanks for your help...DBA in despair!
View 7 Replies
View Related
Jul 29, 2014
I am doing a performance testing for In-memory option is sql server 2014. As a part I want to insert 500 million rows of records into a in-memory enabled test table I have created.
I need a sample script to insert 500 million records into a table ....
View 9 Replies
View Related
May 25, 2015
How to find the equal and opposite rows in a table.
E.g.:
book position
A 500
B -500
C -500
The output should return the rows having equal and opposite positions.
O/P:
A 500
B -500
I have tried self join on a table but in vain.Do we need to cursors for these row level handling?
View 6 Replies
View Related
Jul 6, 2015
I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
it's like there's some massively recursive process going on?
View 5 Replies
View Related
Mar 4, 2008
Hi,
I have issue about how to pivot and merge some rows into one column.
I have the table name : tableA with columns and records as below shown:
Cola Colb
------ ------
A A1
A A2
A A3
B B1
B B2
The request is to display the query like this :
Cola Colb
------- -----------------------------
A A1A2A3
B B1B2
I get stuck with my query below :
select Cola, ???
from tableA
group by Cola
Do you have idea how to achive this with TSQL?
Thanks.
Kasim
View 1 Replies
View Related
Apr 6, 2004
hello,
I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.
Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...
thanks in advance!
View 5 Replies
View Related
Dec 23, 2013
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
View 3 Replies
View Related
Dec 26, 2013
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
View 4 Replies
View Related
Jun 11, 2007
Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)
View 3 Replies
View Related
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
Aug 24, 2015
I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.
View 5 Replies
View Related
Sep 18, 2015
I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.
SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;
View 4 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Jul 15, 2015
I have four columns in my table, the first one is the identity column
col1 Col1 col2 col3
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
When, I see, @@@ sign in my col4, I need to restart the col3 from 1 again so it will look like this
col1 Col2 col3 col4
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
6 12 1 This is another test1
7 12 2 This is another Test2
Is it possible to do that?
View 8 Replies
View Related
Aug 1, 2015
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[Code]....
i want to select the data priority wise
the o/p should look like below
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
View 1 Replies
View Related
