How To Concatenate Multiple Rows Into One Field?
Jul 20, 2005
Hi,
I hope someone here can help me.
We have a product table which has a many-to-many relation
to a category table (joined through a third "ProductCategory" table):
[product] ---< [productCategory] >--- [category]
--------- ---------------- ----------
productID productCategoryID categoryID
productName productID categoryName
categoryID
We want to get a view where each product occupies just one row, and
any multiple category values are combined into a single value, eg
(concatenating with commas):
Product Category
-------------------
cheese dairy
cheese solid
milk dairy
milk liquid
beer liquid
will become:
Product Category
-------------------
cheese dairy, solid
milk dairy, liquid
beer liquid
What is the best way to do it in SQL?
Thanks and regards,
Dmitri
View 4 Replies
ADVERTISEMENT
Oct 18, 2006
I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:
Title - Author - Edition
The Amazing Pixies - A. N. Author - Hdbk
The Amazing Pixies - A. N. Author - Pbk
Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.
View 2 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
Mar 11, 2008
i have a table
View 1 Replies
View Related
Feb 7, 2007
Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.
TABLE
ColumnAColumnB
1.......A
2.......B
3.......C
4.......A
5.......A
6.......B
7.......C
8.......D
9.......C
10......E
EXPECTED OUTPUT
ColumnA ColumnB
1.......4,5
2.......6
3.......7
4.......1,5
5.......1, 4
6.......2
7.......3
8.......
9.......3,7
10......
View 2 Replies
View Related
May 7, 2008
Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like the query result to look like this:1....First Assignment....05/01/2008....553422....Second Assignment...05/03/2008....33456,523433....Third Assignment....05/07/2008....35225,45121,11553Any suggestions would be appreciated!
View 5 Replies
View Related
May 7, 2008
Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:
TableParent (ASSIGNNUM (PK), DESC, STARTDATE)
TableChild (ASSIGNNUM (FK), EMPLOYEENUM)
There could be multiple employees for each assignment. Sample data:
TableParent
1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008
TableChild
1....55342
2....33456
2....52343
3....35225
3....45121
3....11553
I would like the query result to look like this:
1....First Assignment....05/01/2008....55342
2....Second Assignment...05/03/2008....33456,52343
3....Third Assignment....05/07/2008....35225,45121,11553
Any suggestions would be appreciated!
View 12 Replies
View Related
Feb 27, 2008
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
View 10 Replies
View Related
Oct 12, 2005
Hi
I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner
(
@Crime_No varchar(20),
@Unit_ID int
)
as
begin
DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int
DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
SELECT
TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1,
dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id,
dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone,
dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,
TBL_TR_ACCIDENT_VEHICLE.Registration_Number,
TBL_TR_ACCIDENT_VEHICLE.Crime_No,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre,
TBL_TR_ACCIDENT_VEHICLE.vehicle_Make,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model,
TBL_TR_ACCIDENT_VEHICLE.Unit_ID,
TBL_TR_ACCIDENT_VEHICLE.RowID,
TBL_TR_ACCIDENT_VEHICLE.UserID,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color,
TBL_TR_ACCIDENT_VEHICLE.HP,
TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity,
TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle,
TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight,
TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight,
TBL_TR_ACCIDENT_VEHICLE.Skid_Length,
(select TBL_TR_Person_OutsideDetails.OutSide_state from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem-
/*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5))
FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE
WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number)
select @Defect_ID
FROM
tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE
where
tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
AND
TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No
AND
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
end
GO
View 2 Replies
View Related
Jun 27, 2007
Hello,I am loading data from our MS Active Directory into our datawarehouse. (check out Mircosofts's Logparser, it can pull data fromADS, server event logs and more. It can also create text files or loaddirectly to SQL. Its free and a pretty useful tool)There is a field that contains the direct reports of a manager. Thedirect report users are delimited by a pipe symbol.I want to breakup the field into multple rows. There can be none, oneor many direct report users in this field.<disclaimer>This is a snippet of an example. This is only an example. I know thatI have not defined PK nor indexes. My focus is how to solve a problemof parsing a field that has multple values into multple rows.</disclaimer>Thanks for any help in advance.RobCREATE TABLE "dbo"."F_ADS_MANAGERS"("MANAGER_KEY" VARCHAR(255) NULL,"DIRECT_REPORTS_CN" VARCHAR(255) NULL);INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)VALUES ('CN=Marilette, 'CN=RobertD,OU=TechnologyGroup,DC=strayer,DC=edu|CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelleC,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')I want to end up with 5 rows, 1 row for each user that is seprated bythe PIPE symbol.CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Lee K,OU=TechnologyGroup
View 3 Replies
View Related
Nov 27, 2007
HiHo,
just a beginners question:
I have the following row with 2 fields:
Field 1: Task A
Field 2:´ 1;2;3;4
The number of semicolon divided elements in Field 2 is variabel.
I would like to create new rows like:
Row 1 Field 1: A Field 2: 1
Row 2 Field 1: A Field 2: 2
Row 3 Field 1: A Field 2: 3
Row 4 Field 1: A Field 2: 4
I think I should use a Foreach Loop.
But I don't exactly how to do it?
best regards
Chris
View 5 Replies
View Related
Feb 26, 2015
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,
[Code] .....
View 9 Replies
View Related
Jul 20, 2005
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies
View Related
Nov 18, 2007
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
View 2 Replies
View Related
Sep 20, 2007
Hi
I have a table similar to the following:
Date ID Name Job Number JobType
12/12/2007 123456 Fred Smith 111111 Full Day
12/12/2007 654321 Bob Blue 222222 Half Day AM
12/12/2007 654321 Bob Blue 333333 Half Day PM
I need the following output:
Date ID Name Job Number JobType
12/12/2007 123456 Fred Smith 111111 Full Day
12/12/2007 654321 Bob Blue 222222 Half Day AM
12/12/2007 654321 Bob Blue 333333 Half Day PM
Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222
Could someone please point me in the right direction?
Cheers
View 9 Replies
View Related
May 17, 2008
I have a query that returns multiple results
Select fname from table
returns
fred
joe
dave
What I want to do is have the query return only one result like this
"fred, joe, dave"
Any ideas?
Thanks
Kal
View 2 Replies
View Related
Jul 23, 2015
I want to CONCATENATE a field if the rows have the same Id. For example the data looks like
ID Name Identifier
1 ken English
1 Jack French
2 Jamie Spanish
3 Shirley German
The data should look like this if the IDs are same. I am concatenating the Identifier column with the name if the ids are same
ID Name Identifier
1 Ken English English
1 Jack French French
2 Jamie Spanish
3 Shirley German
View 8 Replies
View Related
Oct 7, 2015
 I have a requirement .Where i might have multiple rows one of the row will have description and second row might be related description but wiht routning number null .I want to concatenate these rows
Id
Routingnum
Ddesc
1
101
A
[code]....
View 8 Replies
View Related
Jun 28, 2007
Is there a Data Flow Transromation in SSIS to take multiple columns from an excel sheet and concatinate them into one filed.
Ex:
'Strt-Address', 'City', 'State' into 'HomeAddress'.
I could dump them into a temp table and concatinate the fields and insert that into the destination but that seems like a waiste of using SSIS.
Thanks
View 1 Replies
View Related
Feb 24, 2015
I am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?
This question is just for my understanding
CREATE TABLE #tbl
(id INT IDENTITY(1,1),
item varchar(100))
INSERT #tbl
SELECT 'This'
UNION ALL
[Code] .....
View 4 Replies
View Related
Sep 16, 2015
I have a datetime field for e.g 2015-09-15 00:00:00.000 and an integer field for e.g.100809.
The integer field is actually a time value as in hours,minutes,seconds. For e.g. the value
100809Â means
10-hours
08-minutes
09-seconds
I need to combine the datetime and the int field to get the output as below
2015-09-15 10:08:09
View 7 Replies
View Related
Jul 2, 2007
I have the folliwing two fields, want tp concatenate:
=Fields!sequenceno.Value & =Fields!LogType.Value
Thank you very much for the information.
View 3 Replies
View Related
Dec 5, 2015
I have a table where I need to concatenate all values into one field separated by a comma. Â If the field is null display a blank value. Â This is my table structure and example output
Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)
[code]...
View 5 Replies
View Related
Jan 2, 2007
Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan
View 4 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Dec 25, 2005
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
View 1 Replies
View Related
Jul 23, 2005
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies
View Related
Sep 30, 2015
I need formulating a view through which I can create a an output like below image -
Monthly Table
NUM STATUS ACTIVITYCODE
HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69
Season Table
NUM STARTMONTH STARTDAY ENDMONTH EMDDAY
HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28
[Code] ...
Final OutputÂ
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + Â startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + Â endmonth)AS Season from seasons;
View 10 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
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
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related