Select Data from table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Data from table

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-09-25 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 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

88 Posts

Posted - 2012-09-25 : 14:53:21

Can it be done without temporary table

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-25 : 14:54:26

insert your table name for the temp table

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

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-09-25 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 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

88 Posts

Posted - 2012-09-26 : 04:52:08

Any other way..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 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

88 Posts

Posted - 2012-09-26 : 09:01:21

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 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

88 Posts

Posted - 2012-09-26 : 10:42:27

The current compatibility level is 90.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources