January 20, 2012 at 1:01 am
Hi Experts,
Please help me in this query.
I have a below table.
CREATE TABLE #Table (NAME VARCHAR(10),VALUE NUMERIC(28,10),ID INT)
Inserting values
INSERT #Table
SELECT 'A',10.00000,1
UNION
SELECT 'b',10.00000,1
UNION
SELECT 'C',10.00000,2
UNION
SELECT 'D',10.00000,2
UNION
SELECT 'E',10.00000,2
UNION
SELECT 'F',10.00000,2
UNION
SELECT 'G',10.00000,1
when i execute ,I will get the below result set
NAME VALUE ID
---------- --------------------------------------- -----------
A 10.0000000000 1
b 10.0000000000 1
C 10.0000000000 2
D 10.0000000000 2
E 10.0000000000 2
F 10.0000000000 2
G 10.0000000000 1
Expected result
12
---------- --------------------------------------- -----------
A 10.0000000000 NULL
b 10.0000000000NULL
CNULL10.0000000000
DNULL10.0000000000
ENULL10.0000000000
FNULL10.0000000000
G 10.0000000000 NULL
The thing is we cannot use pivot since the records are Dynamic.
Thanks in advance.
Regards,
Vijay
January 20, 2012 at 2:50 am
maga i have taken some example and produce the result.if any clarification call me
create table Employee
(
UserId BIGINT IDENTITY(1,1),
Name NVARCHAR(300),
Salary INT,
[Year] INT
)
INSERT INTO Employee(Name,Salary,[Year])
SELECT 'Raj',3000,2008
UNION ALL
SELECT 'Raj',5000,2009
UNION ALL
SELECT 'Raj',15000,2010
UNION ALL
SELECT 'Raj',25000,2011
UNION ALL
SELECT 'Vinutha',4500,2010
UNION ALL
SELECT 'Vinutha',12800,2011
Name2008 2009 20102011
Raj3000 5000 15000 25000
Vinutha null null 4500 12800
DECLARE @clmName VARCHAR(1000)
SET @clmName=''
SELECT @clmName=@clmName+'['+CAST([year] AS VARCHAR(10))+'],' FROM Employee GROUP BY [year]
SELECT @clmName=LEFT(@clmName,LEN(@clmName)-1)
EXEC ('SELECT Name,'+@clmName+' FROM
(
SELECT Name,[year],Salary from Employee
) AS P
PIVOT
(
MAX(Salary)
FOR [year] IN ('+@clmName+')
) AS A')
January 24, 2012 at 7:36 am
That is a good responce, never would of thought of using dynamic SQL to pivot.
Thanks,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
January 24, 2012 at 8:30 am
Just when you want dynamic columns. I dont see why Microsoft cant further develop PIVOT to remove the need to use dynamic SQL in this situation. :discuss:
January 25, 2012 at 8:26 am
This would be another solution:
SELECT [NAME],
CASE WHEN [ID] = 1 THEN [VALUE] ELSE NULL END AS Col1,
CASE WHEN [ID] = 2 THEN [VALUE] ELSE NULL END AS Col2
FROM #Table
I hope this could be useful for you.
January 27, 2012 at 3:04 am
* EXAMPLES OF CGSC QUERIES
* Basic Operation
* Tables and Multivalued Fields
* Pattern Matching in Text Fields
* Option Lists
EXAMPLES OF CGSC QUERIES
Here are a few examples of queries for those who are best guided by examples. Be sure to read the more technical and general descriptions that follow, starting with Basic Operation and ending with Option Lists. Note that you can fill in one or more fields and leave as many fields blank as you like. Scroll the query form to see the fields available. The Submit button brings a Select list of records that satisfy your specifications or a single record if only one satisfies those constraints.
*
Find a RecA-deficient, hsdR-minus strain. Go to the Strain Query form and enter recA in one of the Mutations fields and hsdR in the other. Press the Submit button. Examine individual records by clicking on the name of the records retrieved. The records are ordered by the number of mutations that they contain.
*
Find a Tn near a given marker, Go to the Mutation Query form and select Tn from the Type list and enter the range of map coordinates for the marker (in minutes) in the Mutation location boxes. For example if you wanted to find all Tn insertions within one minute of lacZ (located at 7.8 min.), you would enter 6.8 in the first Mutation Location box and 8.8 in the second box. Near the end of each Mutation record, you will find a list of the strains we have that carry that mutation, and you can examine each of those. (DO NOT USE THE SITE FORM TO QUERY FOR Tn's, ALTHOUGH THERE IS A "SITE OF INSERTION" CORRESPONDING TO EACH Tn INSERTION MUTATION.) You can also query by name of the mutation, of course, using either the Strain Query form's Mutation field or the Mutation Query form's Name field, but beware of using the 3-letter "z" name for an insertion not in a known gene, because the name is position-based and may have changed due to changes in map coordinates. It is safer to query for zc%506 or z%506 as a mutation name than zch-506. You can also use the name field rather than the Type list box to search for all insertions, rather than just Tn insertions, by putting %:: in the name field and entering a range in the Mutation Location boxes.
*
Find a deletion that spans part or all of the pst operon: (Things to know: The wildcard in the database is "%". The deletion symbol Delta is represented by "DE". The standard nomenclature for a deletion is of the form Delta(clockwise_gene - counterclockwise_gene)numeral, which tells you the genes known to mark the outermost borders of the deletion and the unique deletion number for that deletion. The database uses standard nomenclature even if a non-standard designation was used in a publication about the mutation, and is restricted to ASCII characters, which must replace, for example, Greek letters.) One way to find such a deletion is to go to the Mutation Query form and enter in the Name field the string DE%pst. Or if your query is more specific, perhaps DE%pstS. (As explained below, the wildcard is automatically appended to the right-hand end of a string in the Name field.) This will retrieve mutations such as DE(phoU-pstS)606(::kan), DE(phoU-pstS)627(::FRT), DE(pstA-pstC)607(::kan), and DE(pstA-pstC)628)::(FRT) for the more general query, and only the first two returns for the more specific query. Examining each Mutation record will lead you to a description of the "Strains carrying this Mutation" and references and definitions. Other successful query strategies include selecting Deletion in the Type list and use %pst in the Name field (this also retrieves deletions within the pstB or pstS gene only), or selecting Deletion in the Type list and entering a Mutation Location range as described above.
Basic Operation
Put desired values or constraints in fields, then press the Submit button. Values in different fields are ANDed, i.e., all constraints must be satisfied for an object to be retrieved.
Tables and Multivalued Fields
In multi-row tables or multi-valued fields, constraints in different rows are ANDed, i.e., all constraints must be satisfied by some (not necessarily the same) row in the retrieved entity.
Pattern Matching
In any field you can use the following pattern matching symbols:
o % matches any string of 0 or more characters, e.g. "abra%" matches "abra" or "abracadabra".
o _ matches any single character, e.g. "a_c" matches "abc".
Matching in text fields is case-insensitive. Note also that text field matching uses automatic completion, so that "adh" matches "adh1", etc. In effect, every pattern ends with '%'. In some cases such as Comment fields a '%' has been automatically appended onto the front of search entries as well. If in doubt, adding a '%' to the beginning of a search field that already automatically appends one will not cause an error, or slow down the query.
Option Lists
Some fields provide lists of selectable options. To select an option, click with the mouse from the dropdown list. To deselect an item, click on the blank entry at the top of the dropdown list.
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing | Pay Per Clicks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply