SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Data from table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goodman2253
Yak Posting Veteran

86 Posts

Posted - 09/25/2012 :  14:41:05  Show Profile  Reply with Quote
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

5155 Posts

Posted - 09/25/2012 :  14:52:23  Show Profile  Reply with Quote
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;
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 09/25/2012 :  14:53:21  Show Profile  Reply with Quote
Can it be done without temporary table
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/25/2012 :  14:54:26  Show Profile  Reply with Quote
insert your table name for the temp table








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 09/25/2012 :  15:08:37  Show Profile  Reply with Quote
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'.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  15:28:23  Show Profile  Reply with Quote
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?
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 09/26/2012 :  04:52:08  Show Profile  Reply with Quote
Any other way..
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  06:50:58  Show Profile  Reply with Quote
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?
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 09/26/2012 :  09:01:21  Show Profile  Reply with Quote
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)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  10:28:38  Show Profile  Reply with Quote
Can you also post the compatibility level of the database? This query will tell you that.
EXEC sp_dbcmptlevel 'yourdatabasename'
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 09/26/2012 :  10:42:27  Show Profile  Reply with Quote
The current compatibility level is 90.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  10:48:30  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000