January 9, 2012 at 6:47 am
Hi every one, happy new year. This is probably a question asked many times, but I can't find a solution to my question. I've a table made up with presently 12 fields, but may change. I want to search across all the fields for a piece of text, then display the fields it appeared in as well as the date each occurance happened, the date is one of the fields. a sample database is below.
date Jim Dave Tony Mike .....
01/Dec/2011 In In out In
03/Dec/2011 In out out In
04/Dec/2011 out in out In
05/Dec/2011 out in in In
so if I searched for 'out' it would return
01/Dec/2011 Tony
03/Dec/2011 Dave
03/Dec/2011 Tony
04/Dec/2011 Jim
04/Dec/2011 Tony
05/Dec/2011 Jim
Any help would be appreciated.
January 9, 2012 at 6:48 am
You're looking for the UNPIVOT keyword, then a filter on the unpivoted results. Books Online has details on the unpivot syntax (it can be a little tricky)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 7:04 am
Thank you so much for getting back to me so fast.I've googled your suggestion but can't see how that can help.
January 9, 2012 at 7:13 am
Unpivot switches the table around so that the column names become values, then you can filter.
If you need example code, then I need table definitions (create table...) and sample data (insert into...) to test.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 7:13 am
just in case it will help I've included a script to build a test table
USE test
GO
-- Creating Test Table
CREATE TABLE testtable(date datetime, jim VARCHAR(20), Dave VARCHAR(20),Tony VARCHAR(20),Mike VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO Product(date,jim,dave,tony,mike)
VALUES('01/Dec/2011','In','In','Out','In')
INSERT INTO Product(date,jim,dave,tony,mike)
VALUES('03/Dec/2011','In','Out','Out','In')
INSERT INTO Product(date,jim,dave,tony,mike)
VALUES('04/Dec/2011','out','In','Out','In')
INSERT INTO Product(date,jim,dave,tony,mike)
VALUES('05/Dec/2011','Out','In','In','In')
January 9, 2012 at 7:20 am
mick burden (1/9/2012)
Thank you so much for getting back to me so fast.I've googled your suggestion but can't see how that can help.
BEGIN TRAN
--Sample data
CREATE TABLE yourTable ([date] DATE, Jim VARCHAR(3), Dave VARCHAR(3), Tony VARCHAR(3),
Mike VARCHAR(3))
INSERT INTO yourTable
SELECT '2011-12-01', 'In', 'In', 'out', 'In'
UNION ALL SELECT '2011-12-03', 'In', 'out', 'out', 'In'
UNION ALL SELECT '2011-12-04', 'out', 'in', 'out', 'In'
UNION ALL SELECT '2011-12-05', 'out', 'in', 'in', 'In'
--Actual query
SELECT date, name
FROM (SELECT [date], Jim, Dave, Tony, Mike
FROM yourTable) tbl
UNPIVOT ([state] FOR name IN (Jim, Dave, Tony, Mike)) AS pvt
WHERE [state] = 'out'
ROLLBACK
January 9, 2012 at 7:21 am
Here you go. Very simple use of UNPIVOT.
SELECT Date ,
Person
FROM ( SELECT date ,
Jim,
Dave ,
Tony ,
Mike
FROM testtable
) test
UNPIVOT ( Direction FOR Person IN ( Jim, Dave, TOny, Mike ) ) AS Unpivoted
WHERE Direction = 'Out'
p.s. your test script throws errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 7:33 am
thanks GilaMonster and Cadavre that's helped me a lot, I'm going to try and get my head round pivot and unpivot to see how this works, meanwhile you've both provided me a greatly appreciated solution.
January 9, 2012 at 7:41 am
There is one more question regarding this, is there a way this can be achieved without specifying the column names (other than the date field) as I may need to use this on a database with lots of fields some of which could change.
January 9, 2012 at 7:57 am
mick burden (1/9/2012)
There is one more question regarding this, is there a way this can be achieved without specifying the column names (other than the date field) as I may need to use this on a database with lots of fields some of which could change.
Dynamic is the way to go -
BEGIN TRAN
--Sample data
CREATE TABLE yourTable ([date] DATE, Jim VARCHAR(3), Dave VARCHAR(3), Tony VARCHAR(3),
Mike VARCHAR(3))
INSERT INTO yourTable
SELECT '2011-12-01', 'In', 'In', 'out', 'In'
UNION ALL SELECT '2011-12-03', 'In', 'out', 'out', 'In'
UNION ALL SELECT '2011-12-04', 'out', 'in', 'out', 'In'
UNION ALL SELECT '2011-12-05', 'out', 'in', 'in', 'In'
--Actual query
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = 'SELECT date, name' + CHAR(10) +
'FROM (SELECT ' + STUFF((SELECT ', ' + name
FROM syscolumns
WHERE id=object_id('yourTable')
FOR XML PATH('')), 1, 2, '') + CHAR(10) +
'FROM yourTable) tbl
UNPIVOT ([state] FOR name IN (' + STUFF((SELECT ', ' + name
FROM syscolumns
WHERE id=object_id('yourTable') AND name <> 'date'
FOR XML PATH('')), 1, 2, '') +')) AS pvt
WHERE [state] = ''out'''
EXEC(@SQL)
ROLLBACK
January 9, 2012 at 7:59 am
syscolumns is deprecated (has been since 2005) and should not be used in new development. Use sys.columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 8:16 am
Many thanks, I've tried your solutions and the first dynamic version from Cadavre works despite the fact syscolumns is in the query, when I substitute this with sys.columns it tells me I have an invalid column named 'id'
January 9, 2012 at 8:21 am
mick burden (1/9/2012)
Many thanks, I've tried your solutions and the first dynamic version from Cadavre works despite the fact syscolumns is in the query, when I substitute this with sys.columns it tells me I have an invalid column named 'id'
Yep, but Gail is correct. You need to modify the query to use sys.columns instead.
BEGIN TRAN
--Sample data
CREATE TABLE yourTable ([date] DATE, Jim VARCHAR(3), Dave VARCHAR(3), Tony VARCHAR(3),
Mike VARCHAR(3))
INSERT INTO yourTable
SELECT '2011-12-01', 'In', 'In', 'out', 'In'
UNION ALL SELECT '2011-12-03', 'In', 'out', 'out', 'In'
UNION ALL SELECT '2011-12-04', 'out', 'in', 'out', 'In'
UNION ALL SELECT '2011-12-05', 'out', 'in', 'in', 'In'
--Actual query
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = 'SELECT date, name' + CHAR(10) +
'FROM (SELECT ' + STUFF((SELECT ', ' + name
FROM sys.columns
WHERE object_id=object_id('yourTable')
FOR XML PATH('')), 1, 2, '') + CHAR(10) +
'FROM yourTable) tbl
UNPIVOT ([state] FOR name IN (' + STUFF((SELECT ', ' + name
FROM sys.columns
WHERE object_id=object_id('yourTable') AND name <> 'date'
FOR XML PATH('')), 1, 2, '') +')) AS pvt
WHERE [state] = ''out'''
EXEC(@SQL)
ROLLBACK
January 9, 2012 at 9:07 am
that's great, thanks for your kind patience
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply