Transact SQL :: Convert Unknown Number Of Questions From Rows Into Columns

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


ADVERTISEMENT

SQL Server 2008 :: How To Pivot Unknown Number Of Rows To Columns Using Data As Column Headers

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

Transact SQL :: Convert Rows Into Columns?

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

Transact SQL :: Convert Rows To Columns?

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

Transact SQL :: How To Convert Table Columns Into Rows In 2005

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

FnParseString And Unknown Number Of Columns

Mar 20, 2007

Keshka writes "I'm using function fnParseString form http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 in some of my sp.

it's very helpfull, but my question is if there is a way to split variable into columns if I don't know how many columns I'll have? It could be 1 or 2 or 3 and etc.


Thanks"

View 3 Replies View Related

How To Present An Unknown Number Of Columns And Their Names

Aug 14, 2001

I've got a database with an unknown number of columns. Hence, the column names are also unknown. What's the easiest SQL to present the values in each column and the column headings?

View 1 Replies View Related

Transact SQL :: Parse Unknown Number Of Data Elements To Multiple Lines

Jun 11, 2015

We are using a table that may give 1 to and unknown number of data elements (ie. years) .   How can we break this to show only three years in each row.  Since we don't know the number years we really won't know the number of rows needed.  Years are stored in their own table by line.  
 
car make year1 year2 year3
A   volare 1995 1996 1997
a   volare 1997   1998   1999
b toyat  1965    1966   1968

We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.

View 8 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Transact SQL :: Convert Non Fixed Rows To Non Fixed Columns Dynamically?

Oct 5, 2015

I have a table with 3 columns  (ID Int , Name Varchar(25), Course Varchar(20))

My source data looks like below

ID      Name        Course
1        A                Java
1        A                C++
2        B                Java
2        B                SQL Server
2        B                .Net
2        B                 SAP
3        C                 Oracle

My Output should look like below...

ID      Name       Course(1)     Course(2)         Course(3)     Course(4)  

1        A                 Java            C++
2        B                 Java            SQL Server .Net             SAP
3        C                 Oracle

Basically need t-sql to Convert non fixed rows to non fixed columns...

Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)

Create SQL:

Create table Sample (ID Int null , Name  Varchar(25) null, Course Varchar(20) null)

Insert SQL:

INSERT Sample (ID, Name, Course)
          VALUES (1,'A','Java'),
                 (1,'A','C++'),
                 (2,'B','Java'),
                 (2,'B','SQL Server'),
                 (2,'B','.Net'),
                 (2,'B','SAP'),
                 (3,'C','Oracle')

View 12 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

How To Convert Rows Into Columns

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

How To Convert Rows Into Columns

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

How To Convert Columns To Rows?

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

Convert Rows To Columns

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

How To Convert Rows To Columns

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

Convert Rows Into Columns

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

T-SQL (SS2K8) :: Way To Convert Rows To Columns

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

Want To Convert Columns Data Into Rows...

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

Convert Columns To Rows - Urgent

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

Convert Rows To Columns In Result

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

Transact SQL :: How To Convert Row To Column With Rows Dynamic

May 21, 2015

I want to convert row to columns with rows dynamic. I have two table, I'll join them and get data. See at

Ok , I try convert rows to columns with PIVOT . i want the result as

GROUP_MOD_IDADDEDITDELETE
G02101

CREATE PROC GET_FUNC @GID CHAR(5)
AS
BEGIN
SELECT FUNCTION_MOD_NAME

[Code] ....

But it's error

Msg 156, Level 15, State 1, Procedure GET_FUNC, Line 13
Incorrect syntax near the keyword 'FOR'.

Note: Type of ALLOW is bit and rows FUNCTION_MOD_NAME is dynamic

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Records From Rows To 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 3 Replies View Related

Convert Repeating Blocks Of Columns Into Rows?

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

Wanna Convert Rows Of A Table As Columns

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

Transact SQL :: How To Number Rows In SELECT

Oct 8, 2015

I have a SELECT statement with multiple JOINs. I want to number the rows returned by this SELECT statement. What's the easiest way to do this?

My current SELECT statement returns this:

ProjectId -- TaskId -- TaskName
123  - 111 -- Do something
123  - 222 -- Do something else
123  - 333 -- Do one more thing

I want to return this:

ProjectId -- TaskId -- TaskName -- Sequence
123  - 111 -- Do something  -- 1
123  - 222 -- Do something else  -- 2
123  - 333 -- Do one more thing  -- 3

View 2 Replies View Related

Transact SQL :: Row Number Over Partition By Multiple Columns

Sep 22, 2015

I have the following query

WITH summary AS
(SELECT tu.SequenceNumber,
tu.trialid,
tu.SBOINumber,
tu.DisplayFlag,

[Code] ....

I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.

Sample Data
SequenceNumber   TrialID      SBOINumber
1                           1            5000
2                           1            5000
3                           2            5000
4                           2            5000
5                           1            5001
6                           3            5001
7                           3            5001

Should return SequenceNumber 1 and 3, 5, 6

View 11 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows And Columns Into Single Row

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

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 View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT     Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No
FROM         Week_Day LEFT JOIN
                      Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
                      Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

Transact SQL :: Convert Multiple Lines With Different Values In Value Columns In 1 Line

Dec 1, 2015

I have this query:

SELECT
          ID1,
          ID2,
          type,
          (case when type = '1' then sum(value) else '0' end) as Value1,
          (case when type = '3' then sum(value) else '0' end) as Value2,
          (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y' 
GROUP BY ID1, ID2, Type

Which returns:
ID1     ID2        Type     Value1     Value2     Value3
005    11547    0          0.00         0.00        279.23
005    11547    1          15.23       0.00        0.00
005    11547    3          0.00         245.50    0.00

And I want to obtain this result:
ID1     ID2        Value1     Value2     Value3
005    11547     15.23       245.50    279.23

View 5 Replies View Related

Transact SQL :: Add Group Number To Similar Rows?

Jun 9, 2015

How can I add a group number to the following query?

For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):

RN                   ReportDate Category                       Division                       TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1                    2015-06-08 Field Sales                    CA BDM                         299743154.3912
2                    2015-06-07 Field Sales                    CA BDM                         299765954.0354
3                    2015-06-01 Field Sales                    CA BDM                         297902654.4172
1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

Becomes

GN   RN                   ReportDate Category                       Division                TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1      1                    2015-06-08 Field Sales                    CA BDM                     299743154.3912
1      2                    2015-06-07 Field Sales                    CA BDM                     299765954.0354
1      3                    2015-06-01 Field Sales                    CA BDM                     297902654.4172
2      1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2      2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
2      3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
2      4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

i.e. each combination of Category+Division results in a new GN.

The query is:

selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN

View 2 Replies View Related

Transact SQL :: How To Count Number Of Specific Columns Of Table

Jul 11, 2015

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

i.e. EmployeeID: 001

week1: 40
week2: 24
week3: 24
week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

View 3 Replies View Related







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