| Author |
 Topic  |
|
|
goodman2253
Yak Posting Veteran
83 Posts |
 Posted - 09/25/2012 : 14:41:05
|
Hi All
I have a table structured which comes by executing the Sql query is
job_id--definition_uid--definition_value
12--A--123
12--B--ABC
12--C--BO
12--D--11
13--A--456
13--B--PQR
13--C--CG
13--D--122
14--A--789
14--B--DFG
14--C--WQ
14--D--567
What I want to do is , for every job when the definition uid there it should print the value
the output expected is
name--group--type--rank
123--ABC--BO--11
456--PQR--CG--122
789--DFG--WQ--576 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/25/2012 : 14:52:23
|
You can do this using PIVOT. See example code below:CREATE TABLE #tmp (jobid int, definition_uid CHAR(1),definition_value VARCHAR(32));
INSERT INTO #tmp VALUES
('12','A','123'),
('12','B','ABC'),
('12','C','BO'),
('12','D','11'),
('13','A','456'),
('13','B','PQR'),
('13','C','CG'),
('13','D','122'),
('14','A','789'),
('14','B','DFG'),
('14','C','WQ'),
('14','D','567');
SELECT
[a] AS [name],
[b] AS [group],
[c] AS [type],
[d] AS [rank]
FROM
#tmp
PIVOT
(MAX(definition_value) FOR definition_uid IN ([A],[B],[C],[D]))P
DROP TABLE #tmp; |
 |
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 09/25/2012 : 14:53:21
|
| Can it be done without temporary table |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2113 Posts |
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 09/25/2012 : 15:08:37
|
the followng error i am getting
Msg 207, Level 16, State 1, Line 2
Invalid column name 'a'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'b'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'c'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'd'.
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/25/2012 : 15:28:23
|
Can you run the two queries below? The version needs to be SQL 2005 or later and compatibility level has to be 90 or greater for the code to work. SELECT @@VERSION;
EXEC sp_dbcmptlevel 'yourdatabasename' If that is so, can you copy the entire code that I posted earlier including the create table statements etc. to a new query window, run it and see if that works? |
|
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 09/26/2012 : 04:52:08
|
| Any other way.. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/26/2012 : 06:50:58
|
| Does that mean that your database version and/or compatibility level is lower than SQL 2005 and 90, or that you are not able to create tables? Can you post the version and compatibility level? |
|
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 09/26/2012 : 09:01:21
|
| Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/26/2012 : 10:28:38
|
Can you also post the compatibility level of the database? This query will tell you that.EXEC sp_dbcmptlevel 'yourdatabasename' |
|
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 09/26/2012 : 10:42:27
|
| The current compatibility level is 90. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/26/2012 : 10:48:30
|
For SQL 2005 compatibility level 90, you should be able to run this code - I tested it on my SQL 2005. This is for testing purposes. You would not need to create the temp table or insert data into the temp table in your actual query.
Once you are able to run this correctly, for you arctual query, simply use the SELECT statement in it after replacing the #tmp with your table name.CREATE TABLE #tmp (jobid int, definition_uid CHAR(1),definition_value VARCHAR(32));
INSERT INTO #tmp VALUES ('12','A','123');
INSERT INTO #tmp VALUES ('12','B','ABC');
INSERT INTO #tmp VALUES ('12','C','BO');
INSERT INTO #tmp VALUES ('12','D','11');
INSERT INTO #tmp VALUES ('13','A','456');
INSERT INTO #tmp VALUES ('13','B','PQR');
INSERT INTO #tmp VALUES ('13','C','CG');
INSERT INTO #tmp VALUES ('13','D','122');
INSERT INTO #tmp VALUES ('14','A','789');
INSERT INTO #tmp VALUES ('14','B','DFG');
INSERT INTO #tmp VALUES ('14','C','WQ');
INSERT INTO #tmp VALUES ('14','D','567');
SELECT
[a] AS [name],
[b] AS [group],
[c] AS [type],
[d] AS [rank]
FROM
#tmp
PIVOT
(MAX(definition_value) FOR definition_uid IN ([A],[B],[C],[D]))P
DROP TABLE #tmp; |
|
|
| |
Topic |
|
|
|
Select Data from table
