SQL Server 2012 :: Convert Records From Rows Into Columns
Nov 13, 2014
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
View 1 Replies
ADVERTISEMENT
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
Nov 13, 2014
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
View 3 Replies
View Related
Jun 2, 2014
I am running sqlserver 2012.
My output data is like
COL1
aaa
bbb
ccc
Now, i want to convert the output to
aaa;bbb;ccc
How can i do this?
View 2 Replies
View Related
Nov 6, 2014
I have idea on SMK, DMK and symmetric and asymmetric keys. I have also idea on TDE. But Is there any way to encrypt all the records of all the columns of a table in a database? actually I need to encrypt the database. Someone .... thinks that when someone will write select query he will get the encrypted records. As per as I am concerned it is not possible. I can encrypt the specific column using symmetric or other keys...
Is there any software or any tool which will provide encrypted records of database?
View 1 Replies
View Related
Aug 28, 2014
I would like to reach out in converting rows to cols.
Format:
NodeName|upsBasicIdentName|upsBasicIdentModel | upsAdvIdentSerialNumber | upsAdvIdentDateOfManufacture | upsBasicBatteryLastReplaceDate
I have this query. the values of each are under status.
SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status
FROM
((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON
[Code] ....
View 3 Replies
View Related
Jan 29, 2014
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0
View 4 Replies
View Related
Feb 18, 2015
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
View 9 Replies
View Related
Mar 26, 2015
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
View 2 Replies
View Related
Aug 15, 2015
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,
[Code] ....
Desired result:
Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200
View 6 Replies
View Related
Mar 18, 2015
I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
View 6 Replies
View Related
Nov 12, 2014
I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.
View 6 Replies
View Related
Oct 28, 2015
I have the following results:
CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110
And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.
View 9 Replies
View Related
Oct 22, 2014
I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
[Code] .....
The results I am looking to achieve are:
PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4
Is there an SQL statement that can be used to accomplish this?
View 1 Replies
View Related
Aug 21, 2014
I've a table like below
ColA ColB
A12
B22
C45
A56
A34
B23
and I need to convert the rows into column names like below
ABCA1A2B1
122245563423
View 1 Replies
View Related
May 29, 2007
how to convert rows into columns.
Data in table
empcode basic
1 1000
2 2000
Required output
empcode 1 2
basic 1000 2000
View 3 Replies
View Related
Nov 13, 2007
Hi there
Assuming I have the following data where the header represents budget cost and the value represent no. of projects
<2K 2K-5K >5K
--------------------
10 15 5
For the above table: the following is my sql:
SELECT
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL<2000000 THEN 1 ELSE 0 END) <2K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 THEN 1 ELSE 0 END) 2K-5K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=5000000 THEN 1 ElSE 0 END) >5K
FROM
PRJ_PROJECTS AS PRJ
How do I program it in sql such that the data will be displayed as below? Thanks
Budget_Range No_of_prj
-----------------------
<2K 10
2K-5K 15
>5K 5
View 5 Replies
View Related
Jul 23, 2005
Hi All,I need to help with converting rows to columns in SQL2k.Input:Id Name Role58Ron Doe Associate58Mark BonasDoctor59Mike JohnsonDoctor59John SmithAssociate102Chris CarterAssociate102Ron Doe Associate102James JonesAssociateOutput should look like:IdDoctorAssoc1Assoc2Assoc358Mark BonasRon Doe NULLNULL59Mike JohnsonJohn SmithNULLNULL102NULLChris CarterRon Doe James JonesThere could be more than 3 associates in the input but I only need 3above columns for associates.I used following query:SELECT Q.sales_id,doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'and Q2.sales_id = Q.sales_id),assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id)FROM view1 QGROUP BY sales_idand I get this error "Subquery returned more than 1 value" since thereare multiple associate for Id 102.Thenks
View 3 Replies
View Related
Apr 24, 2006
hi
i have a sample data like
patientno visit experimentdate
101 1 23-Dec-2004
101 2 23-Mar-2005
101 3 23-Jul-2005
102 1 23-Dec-2004
102 2 23-Mar-2005
102 3 23-Jul-2005
i want it to display like this
patientno visit1 visit1date visit2 visit2date visit3 visit3date
101 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005
102 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005
..please suggest how can we do it
View 4 Replies
View Related
Apr 10, 2008
Hi,
I have a table which consists of some rows
i want to get rows as columns
For eg:
table1
id name dept
1 x a
2 y b
3 z c
i want to write a select query to fetch result as
1 2 3
x y z
a b c
can anyone help please
View 10 Replies
View Related
Sep 17, 2014
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
SELECT 'Total' AS Dept,
[A], [B], [C], [D]
from
[code]....
View 7 Replies
View Related
Apr 3, 2008
Hello All,
I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc....
now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id.
Current Format :-
code mail_address1 mail_address2 mail_address3
1111 abc@yahoo.com xyz@yahoo.com null
Required format:-
code sr_no mail_address
1111 1 abc@yahoo.com
1111 2 xyz@yahoo.com
1111 3 null
i hope that i am clear with my question.
Can any one help me?
Thanks
Prashant
View 4 Replies
View Related
Jan 13, 2008
Hi,
I have a table with 5 columns, from that I need only 3 columns , but the columns should be rows in the output
Ex Table 1
col1 col2 col3
Now I want col1 as one row n col2 as 2nd row and col3 as 3rd row
What query should I write to get this?
Will union work
Thank You
View 1 Replies
View Related
Oct 8, 2015
How can I convert the following data
Create Table tbl_Customer_Bank_Details
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo] varchar(50)
)
[Code] ...
into the following format
Create Table tbl_Customer_Bank_Details2
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo1] varchar(50),
[Code] ....
I am looking at this [URL]
how to do this in SQL 2000.
View 16 Replies
View Related
Apr 24, 2015
i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .
create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )
INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)
[code]...
View 9 Replies
View Related
Apr 30, 2008
Hi I have a 'settings' table similar to:
* Setting_ID
* SettingGroup
* SettingSubGroup
* SettingKey
* SettingValue
example data:
1, Comms, Com1, Speed, 9600
2, Comms, Com1, Parity, N
3, Comms, Com1, DataBits, 8
4, Comms, Com1, StopBits, 1
5, Comms, Com2, Speed, 2400
6, Comms, Com2, Parity, E
7, Comms, Com2, DataBits, 7
8, Comms, Com2, StopBits, 2
I want to have a query like
SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup
where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like:
SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings
----------------
Com1, 9600, N, 8, 1
Com2, 2400, E, 7, 2
I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?
View 1 Replies
View Related
Feb 17, 2015
writing SQL code to convert blocks of columns into rows.
Example,
Id A1 A2 B1 B2
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
into
Id Group Value
1 A 1
1 A 1
1 B 1
1 B 1
2 A 2
2 A 2
2 B 2
2 B 2
View 2 Replies
View Related
Mar 7, 2008
i want to convert the rows of one table to be the column of another table in run time......i have two tables one having student id and name and in another table there are student id, marks and subject as english. hindi and maths in rows...i want to make another a third table which contains student id , name , marks as english, hindi, maths as the column....tried a lot but didnt get the right way...do we have to do it through Join or is there some other technique by which we can solve the query........i dont know wheather i am really informative or not..but am sitting online if incase you wanna know something else even..........
View 4 Replies
View Related
Oct 16, 2014
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
View 0 Replies
View Related
Apr 25, 2014
I have this query
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10
View 9 Replies
View Related
Aug 27, 2015
i have following table columns and i want to convert these all columns into row
SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]
[Code] ....
required output looks like
CASHINHAND 118950
CASHATBANK 200
DEPOSITS 3000
ACCRECEIVABLE
25000
View 3 Replies
View Related
Jun 17, 2015
Using the following tables and data---
CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')
[Code] ...
And currently outputs;
Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million
Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
View 7 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
