In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
I need swapping values in rows of tables based on primary.
I will explain my scenario with the below example.
ID Name Value
--- -------- --------
1 name1 202
2 name1 203
3 name2 203
4 name2 202
5 name3 204
In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.
In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.
UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END
WHERE
Valuein(202,203)
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
_ _ _ _ _ _ _ _
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 RelatedHere is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
I'm trying to run something like this:
Select ID, FIRST(forename), FIRST(surname) from table1
GROUP BY ID;
I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.
There are records like:
ID forename surname
--------------------------------
1 John Kormack
1 James Dope
2 Erin Dupes
3 Will Hugh
3 Walter Heisenberg
So I want to pull out:
1 John Kormack
2 Erin Dupes
3 Will Hugh
How can I run it in TSQL?
I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.
Row data
ID
1
2
3
4
5
6
7
8
9
...
98
99
100
Output will be like
c1 c2 c3 c4 c5....
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16
I have the following result set:
Code:
NameCode1Value1Code2Value2
A1020020250
B20300NULLNULL
CNULLNULLNULLNULL
I want to transform the columns into rows like this:
Code:
NameCodeValue
A10200
A20250
B20300
Any suggestions?
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
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
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
select * from #tmp
I want to convert to @var to same like #tmp table ..
"@" - delimiter goes to rows
"~" - delimiter goes to columns
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies View RelatedI want to filter out the duplicate rows based on three columns. I got this quick query from Microsoft site to filter out the duplicate rows, but I am getting the result that filters out the non-duplicate one too. Below is the query
;With Temp as (
SELECT row_number() over (partition by [id],[p_date], order by [id],[p_date],) as Row,
[code]...
In the above case id is null, but in some rows id is not null . The above is obviously not duplicate.
Hi,
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
Thanks in advance!!!
sqlnovice123
I'm building a report that has a column in the table called countries of origin, that column can contain data like "USA","China" ( normal countries) but some rows can also contain "USA, China, Australia" ( multiple counties in one row).
I'm trying to build a filter to filter my results :
-I have a data set created with a list of all world countries
-I've Build a Parameter containing a drop down list with all countries
--- My problem is when I want to create the filter in my main data set , I can so it , but when I search for example for "USA" , I don't get the lines where there are multiple countries and "USA" is one of them...
There must be an expression in the filter that will be able to search through all the values in the string and return me my correct results.
Link : [URL] .....
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
Genre - Western: Movie1, Movie2, Movie3
Language - English: Movie2, Movie4, Movie5
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
MS SQL 2008 R2
I have the following effectively random numbers in a table:
n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers
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.
I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need.
I have a Table/View called [Shipment] with the data below.
ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA
I want the following as my result.
ShipNo Supplier1 Supplier2
==========================
1 CFA TFA
2 LRA LRB
3 AB
4 TFA
Thx.
Rav
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!
I have the following result query :
Which is produce by following SP :
SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
[code]....
What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?
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.
I'll show my schema first, then I'll explain what I'm doing:
--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------
--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------
--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------
--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------
--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------
--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------
There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).
A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.
For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:
----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3
So in that example, that event had 3 sessions.
Right now, I can only output E_Title and ER_Name, I don't know how to output the session information
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
A happy belated New Year to all!
Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?
example
Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'
I want to use the month and year of the date to rename the column.
Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'
Thank you in advance for any help you can offer.
TMendez
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?
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!