टी-एसक्यूएल: पंक्तियों को कॉलम में परिवर्तित करने वाली तालिकाओं में क्वेरी?

Question

एसक्यूएल सर्वर 2012।

सीधे बिंदु पर, मैं यही करने की कोशिश कर रहा हूं:

उपयोगकर्ता तालिका

id   username fullname
---- -------- ----------------------
1    test0001 Test User #1
2    test0002 Test User #2
3    test0003 Test User #3
4    test0004 Test User #4

झंडा तालिका

id  name       description
--- ---------- -------------------------------------------
1   isActive   true if user is currently active
2   isAdmin    true if user can do Admin things
3   canEdit    true if user can can edit

उपयोगकर्ता ध्वज तालिका

user flag
---- ----
1    1
1    2
1    3
2    1
2    3
3    1

(user = FK to user.id, flag = FK to flag.id)

वांछित परिणाम

userId username isActive isAdmin canEdit
------ -------- -------- ------- -------
1      test0001 1        1       1
2      test0002 1        0       1
3      test0003 1        0       0
4      test0004 0        0       0

संक्षेप में, मैं प्रत्येक ध्वज को फ़्लैग टेबल में name कॉलम हेडर के रूप में उपयोग किए जाने वाले कॉलम में बदलना चाहता हूं। फिर मैं प्रत्येक उपयोगकर्ता के लिए एक पंक्ति चाहता हूं, प्रत्येक कॉलम में एक बूलियन के साथ यह दर्शाता है कि उनके पास वह विशेष ध्वज है या नहीं।

इसे अनुकूलित करने में सक्षम होने की आवश्यकता है - उदा। यदि कोई अन्य ध्वज जोड़ा जाता है, तो क्वेरी के परिणाम में उस ध्वज के नाम के साथ उसके शीर्षक के रूप में एक अन्य स्तंभ होना चाहिए।

मैं इसे एक दृश्य में करना पसंद करूंगा, लेकिन मैं तालिका-मूल्यवान फ़ंक्शन के साथ ठीक रहूंगा।

मैंने पहले ऐसा कुछ नहीं किया है इसलिए मुझे यह भी यकीन नहीं है कि कहां से शुरू करना है - मैं टेबल पर पूर्ण रूप से शामिल हो सकता हूं और प्रति उपयोगकर्ता प्रति ध्वज के साथ एक पंक्ति के साथ समाप्त हो सकता हूं, लेकिन फिर मैं इसे नीचे फोल्ड करना चाहता हूं प्रति उपयोगकर्ता एक पंक्ति।

संपादित करें प्रमुख बिंदुओं में से एक है "अनुकूलन करने में सक्षम" - सबसे अच्छा परिदृश्य एक क्वेरी होगी जो प्रतिक्रिया बनाते समय ध्वज तालिका से स्वचालित रूप से सभी वर्तमान में परिभाषित ध्वजों को खींचती है . क्वेरी को संपादित करना आवश्यक रूप से खराब नहीं है, लेकिन उस उदाहरण पर विचार करें जहां एक व्यवस्थापक को सिस्टम में एक नया ध्वज जोड़ने की अनुमति है। एक नया ध्वज सम्मिलित करना आसान है, इसे प्रतिबिंबित करने के लिए संग्रहीत क्वेरी को स्वायत्त रूप से संपादित करना बहुत कठिन है। यदि ऐसा करना संभव नहीं है, तो एक स्पष्टीकरण क्यों उपयोगी होगा। धन्यवाद!

sql-server

0

fdmillion 11 सितंबर 2017, 20:28

Answer 1

अगर आपको मेरी तरह pivot फंक्शन पसंद नहीं है; आप इस तरह SUM IIF विधि का उपयोग कर सकते हैं

SELECT u.id
    , username
    , SUM(IIF(flag = 1, 1, 0)) AS isActive
    , SUM(IIF(flag = 2, 1, 0)) AS isAdmin
    , SUM(IIF(flag = 3, 1, 0)) AS canEdit
FROM User u
LEFT JOIN UserFlags uf ON uf.[user] = u.id
GROUP BY u.id
    , username

2

irfandar 11 सितंबर 2017, 21:12

Answer 2

आप इसे केस स्टेटमेंट और सबक्वेरी या पिवट के साथ कर सकते हैं। वे दोनों अनिवार्य रूप से एक ही काम करते हैं। इसमें मेरे द्वारा परीक्षण के लिए बनाई गई तालिकाओं के लिए डीडीएल शामिल है।

declare @user_tbl table (
id int,
username nvarchar(max)
)

INSERT @user_tbl VALUES
(1,'test0001'),
(2,'test0002'),
(3,'test0003'),
(4,'test0004')


declare @userflags_tbl table(
userid int,
flag int
)

INSERT @userflags_tbl VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(2,3),
(3,1)

declare @flags_tbl table(
id int
)

INSERT @flags_tbl VALUES
(1),
(2),
(3)

SELECT
    userid,
    username,
    MAX(isActive) AS isActive,
    MAX(isAdmin) AS isAdmin,
    MAX(canEdit) AS canEdit
FROM (
    SELECT
        user_tbl.id AS userid,
        user_tbl.username,
        CASE
            WHEN userflags_tbl.flag = 1 THEN 1
            ELSE 0
            END AS isActive,
        CASE
            WHEN userflags_tbl.flag = 2 THEN 1
            ELSE 0
            END AS isAdmin,
        CASE
            WHEN userflags_tbl.flag = 3 THEN 1
            ELSE 0
            END AS canEdit
    FROM @user_tbl user_tbl
    LEFT JOIN @userflags_tbl userflags_tbl ON
        user_tbl.id = userflags_tbl.userid
    LEFT JOIN @flags_tbl flags_tbl ON
        userflags_tbl.flag = flags_tbl.id
    )tbl
GROUP BY
    userid,
    username


SELECT
    userid,
    username,
    ISNULL([1],0) AS isActive,
    REPLACE(ISNULL([2],0),2,1) AS isAdmin,
    REPLACE(ISNULL([3],0),3,1) AS canEdit
FROM (
    SELECT
        user_tbl.id AS userid,
        user_tbl.username,
        userflags_tbl.flag
    FROM @user_tbl user_tbl
    JOIN @userflags_tbl userflags_tbl ON
        user_tbl.id = userflags_tbl.userid
    JOIN @flags_tbl flags_tbl ON
        userflags_tbl.flag = flags_tbl.id
    ) tbl
PIVOT (
    MAX(flag)
    FOR flag IN ([1],[2],[3])
    ) AS PivotTable

0

Andrew O'Brien 11 सितंबर 2017, 20:58

टी-एसक्यूएल: पंक्तियों को कॉलम में परिवर्तित करने वाली तालिकाओं में क्वेरी?

2 जवाब

संबंधित सवाल

नए सवाल

sql-server